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 6, “Statistics Tables and Displaying Statistics with optdiag.” in the Performance and Tuning: Monitoring and Analyzing for Performance book.
