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.

In the summary section we 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.