INF: Search Arguments That Determine Distribution Page Usage

This article was previously published under Q169642
This article has been archived. It is offered "as is" and will no longer be updated.
In determining the existence of useful indexes to resolve a query, SQLServer looks for the search arguments in the query. Search arguments arethe arguments in the WHERE clause of a query that help to specify acondition to restrict the result set returned by the query. It is necessarythat the arguments in the WHERE clause are of the form "column operatorconstant". If indexes exist on these columns, the SQL Server optimizer canestimate the selectivity of the index and thereby decide whether or not touse it.

It is desired that the optimizer estimate search argument selectivity basedon the distribution information available in the distribution page. Othermethods of determining index selectivity for a search argument includeusing index densities and magic density. Magic density is an estimate ofthe search argument selectivity that can be used when density or statisticson the distribution page cannot be used. It estimates 10 percent of therows match for an equality comparison, 25 percent for a between comparison,and 33 percent for greater than, less than, greater than or equal to, andless than or equal to comparisons.
Scoring an index is the process of estimating the usefulness of the indexfor the search argument in the WHERE clause. Distribution steps aremaintained only for the first column of the index specified. A valid searchargument is of the form "column operator constant". Any operator that isvalid on the column specified can be used. Invalid search arguments mayprevent the index from being used.

To allow the optimizer to do index scoring for a search argument based onthe distribution page, the following rules on search arguments may behelpful:
  1. 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.

  2. 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.
  3. 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).
  4. 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.
  5. For join clauses, a distribution page cannot be used (unlike for a search argument).
For more details on writing efficient queries, refer to the followingarticles in the Microsoft Knowledge Base:
110352 : INF: Optimizing Microsoft SQL Server Performance

46434 : INF: Writing Efficient Queries and Stored Procedures

Article ID: 169642 - Last Review: 10/07/2013 03:52:04 - Revision: 3.0

  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive kbinfo kbusage KB169642