Statistics - Pro SQL Server Internals - page 52

Figure 3-1. DBCC SHOW_STATISTICS output
As you can see, the DBCC SHOW_STATISTICS command returns three result sets. The first one contains general
metadata information about statistics, such as name, update date, number of rows in the index at the time when the
statistics were updated, and so on. The Steps column in the first result set indicates the number of steps/values in
the histogram (more about this later). Density is calculated based on the formula: 1 / frequency , where frequency
indicates the average number of the duplicates per key value. The query optimizer does not use this value, and it is
displayed for backwards-compatibility purposes only.
The second result set contains information about density for the combination of key values from the statistics
(index). It is calculated based on 1 / number of distinct values formula, and it indicates how many rows on
average every combination of key values has. Even though the IDX_Books_ISBN index has just one key column ISBN
defined, it also includes a clustered index key as part of the index row. Our table has 1,252,500 unique ISBN values,
and the density for the ISBN column is 1.0 / 1,252,500 = 7.984032E-07 . All combinations of (ISBN, BookId)
columns are also unique and have the same density.
The last result set is called the histogram . Every record in the histogram, called a histogram step , includes the sample
key value from the leftmost column from the statistics (index) and information about the data distribution in the range of
values from the preceding to the current RANGE_HI_KEY value. Let's examine histogram columns in greater depth.
The RANGE_HI_KEY column stores the sample value of the key. This value is the upper-bound
key value for the range defined by histogram step. For example, record (step) #3 with
RANGE_HI_KEY = '104-0100002488' in the histogram from Figure 3-1 stores information
about the interval of: ISBN > '101-0100001796' and ISBN <= '104-0100002488' .
The RANGE_ROWS column estimates the number of rows within the interval. In our case, the
interval defined by record (step) #3 has 8,191 rows.