Microsoft® SQL Server 2012 Unleashed

Managing the Optimizer

Because the Query Optimizer might sometimes make poor decisions as to how to best process a query, you need to know how and when you may need to override the Query Optimizer and force SQL Server to process a query in a specific manner.

How often does SQL Server require manual intervention to execute a query optimally? Considering the overwhelming number of query types and circumstances in which those queries are run, SQL Server does a surprisingly effective job of query optimization in most instances. For all but the most grueling, complex query operations, experience has shown that SQL Server’s Query Optimizer is quite clever—and very, very good at wringing the best performance out of any hardware platform. For this reason, ...