INF: Search Arguments That Determine Distribution Page Usage (169642)

SUMMARY

In determining the existence of useful indexes to resolve a query, SQL Server looks for the search arguments in the query. Search arguments are the arguments in the WHERE clause of a query that help to specify a condition to restrict the result set returned by the query. It is necessary that the arguments in the WHERE clause are of the form "column operator constant". If indexes exist on these columns, the SQL Server optimizer can estimate the selectivity of the index and thereby decide whether or not to use it.

It is desired that the optimizer estimate search argument selectivity based on the distribution information available in the distribution page. Other methods of determining index selectivity for a search argument include using index densities and magic density. Magic density is an estimate of the search argument selectivity that can be used when density or statistics on the distribution page cannot be used. It estimates 10 percent of the rows match for an equality comparison, 25 percent for a between comparison, and 33 percent for greater than, less than, greater than or equal to, and less than or equal to comparisons.

MORE INFORMATION

Scoring an index is the process of estimating the usefulness of the index for the search argument in the WHERE clause. Distribution steps are maintained only for the first column of the index specified. A valid search argument is of the form "column operator constant". Any operator that is valid on the column specified can be used. Invalid search arguments may prevent the index from being used.

To allow the optimizer to do index scoring for a search argument based on the distribution page, the following rules on search arguments may be helpful:

Avoid inequality operators (as in column != constant). This is not a valid search argument, and does not allow the optimizer to evaluate the usefulness of the index on this column, if any.
Reduce wherever possible the use of local variables (as in column = @local variable), unless the value of the @local variable can be determined before run time. This is because the value of the @local variable is not known at compile time. This may lead to the optimizer using magic densities or index density. When the value is not available, it cannot be checked against distribution steps. The local variable will be used as a valid search argument if it is passed as a parameter to a stored procedure.
Try to avoid performing any operations on the column (as in column*100 = constant). This is not treated as a valid search argument. Instead, try rewriting the same expression (as in the form column = constant/100).
For subqueries like "...where column operator (select column from table)" the optimizer may not be able to use the distribution steps because the value of the constant expression is not known until the query is executed.
For join clauses, a distribution page cannot be used (unlike for a search argument).

For more details on writing efficient queries, refer to the following articles in the Microsoft Knowledge Base:

110352 : INF: Optimizing Microsoft SQL Server Performance

46434 : INF: Writing Efficient Queries and Stored Procedures