Display detailed index statistics

A stored procedure that allows you to obtain detailed DBCC SHOW_STATISTICS information on all indexes for a given table.

Index tuning in Microsoft SQL Server isn't rocket science...It's much, much harder! This is especially true if you are administering a multi-gigabyte database with dozens of tables, which is constantly being hit by all kinds of end-user queries and updates.

Thankfully, SQL Server includes a DBCC command called DBCC SHOW_STATISTICS, allowing you to get a detailed view on how your indexes might be expected to perform. Accepting two arguments (table name and index name), this function shows, among many other things, the columns included in your index and your index's "density" (that is, its uniqueness). Since an index is of little value unless it is used on a column (or series of columns) containing a high percentage of unique values, this function can help you determine if you need to reconfigure your indexes.

The problem with the DBCC SHOW_STATISTICS command, however, is that you can only run it on one index at a time. This can be tedious if your tables each contain several indexes, or if you don't know the names of all your indexes (as can happen if you're taking over the administration of a database that was developed by someone else). In such a case you would need to query the sysindex table or run sp_helpindex to obtain the index names for the table, and then run the DBCC command on each index.

Here's a simple stored procedure I created called sp_indexstats that allows you to obtain detailed DBCC SHOW_STATISTICS information on all indexes for a given table. Sp_indexstats takes a single parameter (@tablename) and returns information on all its indexes. You could even take this a step further, if you wanted to display information on all indexes in all tables, by executing this stored procedure in an sp_MSForEachtable statement.

Note that I created this procedure in SQL Server 7 using temporary tables. If you are using SQL 2000, you can replace these with table-type variables for better performance. Here it is:

create procedure sp_indexstats 
@tablename nvarchar(256) 


create table #indexes 
(id int identity, name nvarchar(256)) 

insert into #indexes (name) 
select name from sysindexes 
where id = object_id(@tablename) 

declare @i int 
set @i = 1 

declare @index nvarchar(256) 

@i < (select max(id) from #indexes) 
        select @index = name 
        from #indexes 
        where id = @i 

        select 'Stats for ' +     
        @tablename + ', ' + @index 
        dbcc show_statistics  
        (@tablename, @index) 
        set @i = @i + 1 

For More Information

  • What do you think about this tip? E-mail us at [email protected] with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on SQL Server Database Modeling and Design