This post goes through the SQL Server DBCC command which deals with displaying the actual underlying statistics to a particular SQL Server index.

Statistics are vital for optimising SQL query performance, SQL Server uses index statistics of all the underlying tables when trying to satisfy the processing of a query, ensuring that your statistics are up-to-date, especially if the underlying tables have experienced a big change percentage, will yield a more efficient execution plan.

In order to query the SQL Server statistics for a particular index, you can use the following t-SQL command:

the WITH statement above is optional, and when omitted, the 3 main statistics parts are returned, these parts are:

• STAT_HEADER: Shows information such as when the last time statistics were updated, how many rows were sampled, if the index filtered any rows, etc.
• DENSITY_VECTOR: Shows the index density vector, which is indicates the density of values across the index columns.
• HISTOGRAM: This is where the real statistic juice is, and contain information vital to the index operation and pertinacity in a particular query situation. The Index Historgram represents the steps (max 200 steps), each step partitions the index’s values, this is stored in an efficient manner that allows SQL Server to readily access index statistics with minimal over-head in terms of storage.

SQL Server 2008 and upwards also provide the String Summary Statistics, which allows SQL Server to store statistical information regarding string based indexes, since this is a much more complex process than any numeric or base type. Currently there is no way of getting back information about the String Summary Statistics. It is interesting to note that if a string value is larger than 80 characters, the String Summary Statistics will store information about the first 40 and the last 40 characters of a string, ignoring the middle bit.

An example using the DBCC command above can be seen below.

For more information about SQL Server Index Statistics please refer to Statistics Used by the Query Optimizer on MSDN or Stairway to SQL Server Indexes on SqlServerCentral