When you create an index, statistics are generated and stored in system tables. Some of the statistics relevant to determining the cost of search arguments and joins are:

Statistics about the index: the number of pages and rows, the height of the index, the number of leaf pages, the average leaf row size.

Statistics about the data in the column:

A histogram for the leading column of the index. Histograms are used to determine the selectivity of the SARG, that is, how many rows from the table match a given value.

Density values, measuring the density of keys in the index.

Cluster ratios that measure the fragmentation of data storage and the effectiveness of large I/O.

Only a subset of these statistics (the number of leaf pages,
for example) are maintained during query processing. Other statistics
are updated only when you run **update statistics** or
when you drop and re-create the index. You can display these statistics
using **optdiag**.

See Chapter 37, “Statistics Tables and Displaying Statistics with optdiag.”