SQL Statistics

Statistics are only maintained on the first column of a composite index. Because of this, SQL Server’s Query Optimizer might not make as good use of composite indexes as it could. If you suspect that a composite index is not being used as you expected it would be, you will want first to verify that the index is not being used by examining the query’s execution plan.

If you see that it is not being used, and it should be, consider adding a hint to force the index to be used, then see if using the index really helps performance. If it does, keep the hint. If it does not help performance, drop the hint and the composite index that is not being used, assuming that it is not needed by another query.

An index that has a distinct value is a selective index. Therefore, a unique index has a really high selective quotient as there are no replica entries. That is, it has potential for being called more often. Invariably, this follows that an indexed column with non-distinct values has selectivity that is much less.

Our SQL Server database can keep a track on these statistics and therefore have information on the potential of each index being selective or not. The query analyzer then uses these indexes in establishing whether to choose a particular index or not while execute our particular query in question. Hence, when we want to generate a query execution plan, we can use these statistics for our requirement. So it’s not a bad idea to spend a little bit of extra time and try to figure out the indexes that our application might use the most.

How Statistics Work

Keeping statistics up to date is crucial for optimal performance of a database. In some cases, however, an available execution plan is not ideal for the given situation. To understand when and why this happens, it is important to understand how statistics work.

The following command displays the statistics for a given index on a given table

USE MyDatabase
DBCC SHOW_STATISTICS (table_name, index_name)

To find index_name for your table you can use this query:

select i.name,* from sys.sysindexes i
inner join sysobjects tbls on i.id = tbls.id
where tbls.name = ‘table_name”

This gives all statistical information on this index. Among the date and time of the last update and it’s density, it returns samples of the index.

Statistics are the next performance booster that we can look into after getting our indexes right and optimized. Statistics contain the information, distribution and have the task of deciding the selectivity of the indexes that we have defined. This is useful because our query optimizer can use this information retrieved from SQL Statistics to choose the least expensive path that would enhance our query performance.