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

Blog_TooManyIndexes_thumb.png

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.

FriendsOfRedGate_2011_OnWhite_RGB_300px.

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.

Keep up to date with Simple-Talk

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

This post has been viewed 1588 times – thanks for reading.

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

Jonathan Allen

View all articles by Jonathan Allen