INF: Understanding Page Estimates (170764)

SUMMARY

When scoring a search or join clauses, the Sql Server optimizer uses the row and page estimates to arrive at the cost associated with using the index. The page estimate is the estimated number of pages that will need to be traversed in finding a matching row. The page estimates (costing x pages in trace(302) output) is used in estimating the cost of using the index. When there are no indexes being used, the cpages value will be the actual number of pages in the table. This value will be lower than the total number of pages in the table when a useful index exists and is being considered by the optimizer. Relevant portions of the trace(302,310) are used in this discussion. This is important because the total cost associated with using an index for a search argument or in evaluating the cost of a join permutation depends on the cpages value.

MORE INFORMATION

Cost comparisons are done between the table scan cost and the estimated cost of using the index. The cpages value contributes to this estimated cost, and is affected by the number of levels in the index btree, which in turn depends on the width and type of index, and number of rows and pages in the table.

It also depends on row estimates (crows). Crows is an estimate of the number of rows expected to satisfy the search or join clause. Crows is equal to the total rows in the table if there are no search clauses on the table.

This information can be seen under q_score_join portion of the trace(302) output under the scoring clause for index section.

Entering q_score_join() for table 't1' (varno 0). Scoring clause for index x Estimate: indid x, selectivity y, rows a pages b Cheapest index is index X, costing B pages and generating A rows per scan. Note that the same cpages value (B pages) can be found under the j_optimize section of the trace(310) output under the jplan for the varno and indexid representing the table in q_score_join.

   JPLAN (0x28cf49c) varno=0 indexid=x
   .... cpages=b....

Cpages is also used to calculate logical page reads (lp in trace flag 310 output).