How SQL Server Determines an Execution Plan Using Available Indexes and Statistics

The last part of this output is important. On the basis of these records, SQL determines the expected number of hits for a search parameter. The output has the following entries:

RANGE_HI_KEY: The upper value of the histogram step. For example, 'Smit'

RANGE_ROWS: The number of rows of the histogram step, excluding the occurrences of the upper bound. For example, there are 66,811 rows between 'Smit' and 'Snijders'.

EQ_ROWS: The number of rows equal to the value of the upper bound of the histogram step. For example, there are 48,760 rows with the value 'Smit'.

DISTINCT_RANGE_ROWS: The number of distinct values in the histogram step, excluding the upper bound. For example, there are 462 distinct values between 'Smit' and 'Snijders' (not including either).

AVG_RANGE_ROWS: The average number of occurrences for each unique value in the histogram step. This is calculated as RANGE_ROWS / DISTINCT_RANGE_ROWS. For example, between 'Smit' and 'Snijders' are 66811 rows with 462 distinct values, resulting in an expected 144.61255 rows for unique value between 'Smit' and 'Snijders'.

The Estimated Execution Plan for a search for 'Smits' (between 'Smit' and 'Snijders') confirms this number:

Figure 2: Estimated Execution Plan - estimated row count 'Smits'

On the basis of this estimate, the following steps in an execution plan will be determined.

If a search value is matched in the statistics table, the value for the EQ_ROWS will be the estimated row count, as can be seen in the case for searching for 'Smit':

Figure 3: Estimated Execution Plan - estimated row count 'Smit'