An index is a physically implemented structure in the database (you can read up more in BOL on clustered and non-clustered indexes) whereas statistics are a set of values that help the optimizer during the execution plan formation stages to decide whether to use an index or not. And it is not a 1-1 relationship between indexes and statistics i.e. all indexes have statistics but one can have statistics without an index. And these statistics that do not associate to an index can also help in the formation of the right execution plan. We will cover that in another post as to how that can help. In this post, let’s cover the basics of these statistics.
An index helps the optimizer to find the data during the execution of the statements and statistics help the optimizer to determine which indexes to use.
So, what exactly do statistics contain that make them so useful? Statistics essentially contain two pieces of information:
1) A histogram which contains a sampling of the data values from the index and the distribution of the values in the ranges of data
2) Density groups (collections) over the column (or number of columns) of a table or an indexed view. Density essentially reflects the uniqueness of the values in a particular column.
No comments:
Post a Comment