Microsoft® SQL Server 2012 Unleashed

Row Estimation and Index Selection

When the query analysis phase of optimization is complete and all SARGs, OR clauses, and join clauses have been identified, the next step is to determine the selectivity of the expressions (that is, the estimated number of matching rows) and to determine the cost of finding the rows. The costs are measured primarily in terms of logical and physical I/O, with the goal of generating a query plan that results in the lowest estimated I/O and processing cost. Primarily, the Query Optimizer attempts to identify whether an index exists that can be used to locate the matching rows. If multiple indexes or search strategies can be considered, their costs are compared with each other and also against the cost of a table ...