14 December 2011

How many indexes is the right amount?

I would be deafened by the chorus of DBA’s shouting “It depends” at me after a question like that in person, I somehow feel safer from the far side of Live Writer.

I have recently been looking at the number of indexes in one of my databases and have found a curiously high number of indexes in comparison to the number of columns.

Right, all the code now and some discussion underneath it

So this query collects data regarding column counts per table and then counts indexes per table and then merges (JOINS) them together to compare the details. When I am querying data like this and want to analyse it in a variety of ways I prefer to insert it into a temporary table. A personal preference.

The first extract from the temporary table is purely a summary of the data – how many tables are there in each category of concern? The last query lists the whole data set.

Running this against my (somewhat battered) copy of AdventureWorks we get the results as below


In the summary section we can see that 21 tables (29% of the tables in the database) have an index count that is more than half of the column count. In the detail in the second grid we can see that one table has 5 indexes on a table with 4 columns.

Now I am not promoting any particular ratio of indexes to columns, all I am suggesting is that keeping an eye on this might give you a pointer that you are adding too many indexes and possibly altering existing ones would be a better solution for the performance of your server. Do you have any thoughts on whether this is a good way, or a worthwhile way to review your indexes?

Feel free to alter the percentages in the TSQL to something that you feel more appropriate.


Usual conditions apply. Don’t trust this code on your production servers without reading and understanding how it works. You will not get any support if this code does bad things.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 0    Average: 0/5]

Jonathan Allen has been a SQL Server DBA since 1999, most enjoying performance tuning and development but also working with SSIS, SSRS to provide suitable business solutions. He is SQLSouthWest PASS Chapter Leader, blogs for Simple Talk, is a forum moderator at ask.sqlservercentral.com and is on Twitter. If you would like to find your nearest user group or just want to say hello then he would love to get an email from you.

View all articles by Jonathan Allen