Guru's Guide to SQL Server Architecture and Internals, The

Recap

Using indexes, statistics, and the submitted query text as input, the SQL Server query processor produces optimized execution plans that the server then carries out to access and return the requested data. The server can make use of clustered and nonclustered indexes as well as auto-generated and manually created statistics. It can use multiple indexes per table within a given query plan and can intersect and join indexes.

The query processor picks the plan with the lowest cost. Usually, this is driven by the estimated I/O for the plan, but there are other cost factors as well. The estimated I/O for each step in a plan is generally based on the estimated number of rows it will return for each execution and the estimated number of executions. ...