Microsoft SQL Server 2014 Unleashed

Should You Override the Query Optimizer?

You might be wondering, how often does SQL Server require manual intervention to execute a query optimally and should I try and override the Query Optimizer when it seems to be making a bad choice? The vast majority of the time, if index statistics are up to date and queries are well written with optimizable search arguments (SARGS) and join clauses, the query optimizer generates and selects an effective and efficient query plan. A lot of research and effort over the years has gone into developing the algorithms that the optimizer uses, and it’s a rare occasion when you’ll find that it “gets it wrong” and you have to “coerce” it into generating a different query plan. For this reason, you should treat ...