Quick Script: Index Sizes

In the world of performance monitoring, there a number of ways to identify problems. Each method is different and usually, neither way is better than another.

Over the years, I’ve started look at metrics around non-clustered indexes to help find problems.   Once while working at a client site, I was astounded by how they would implement changes so rapidly.  This quick rate of change usually had detrimental effects on the performance of the application. Of course as usual, once the change was in Production, it was difficult to fix things.

One day, I happened to be looking at their flagship application’s database.  The table I was focused on was about 60GB in size.  This, in itself, was not a cause for alarm.  However, the 120GB worth of non-clustered index space was.  Did this mean that we had a performance issue?  Not necessarily.  In my opinion, this meant that we had a potential of having a performance issue.

Here is the common problem that I see.

  1. Ted writes a query for the application.
  2. Ted knows he needs an index so creates a non-clustered index to cover his query.  Awesome!
  3. Paul also writes a query for the application
  4. Paul is just as smart as Ted and so he writes another non-clustered index for his query.
  5. Rinse and Repeat

Shockingly, this occurs more often than you’d think.  Instead of checking to see what indexes are already in place that might cover their respective queries, they just blindly create a new one.

To help identify this potential issue, you could just compare the total number of non-clustered indexes on the table, however in my opinion that doesn’t tell you the whole story.  You don’t know how large those indexes are so your story is not complete.

The script below will give you a better picture.  It compares the heap/clustered index size against the aggregate size of the non-clustered indexes.   The script is configured to identify indexes that are three times larger than table itself.  Your mileage may vary and you can certainly adjust to your liking.

If you run the script against one of your databases and the script alerts you to a potential issue, you will have to further evaluate that particular table.

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it very thoroughly.  Period.  

This script can be useful to help identify potential issues.   It is just another tool for your toolbox.


© 2015, John Morehouse. All rights reserved.

One Reply to “Quick Script: Index Sizes”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.