Check Your Indexes

We all know that we’re supposed to check for missing indexes. But what about unused or underused indexes. How often do you check for those? My guess is that a lot of people don’t check for them. I know I didn’t… until Friday. It’s database migration season here in central Ohio, and in order to move databases to new servers, you need to physically copy files. To make that operation fast you want to copy as little data as possible. Here’s the thing: we often only think about the amount of space that an index is going to take up before we add it to a table, but how often do you think about how much space indexes are taking up after you’ve added the index to the table? Over the course of 3 minutes, I identified 20 gigabytes of unused indexes on just one database. That’s a staggering amount of space being taken up by indexes with no purpose. In addition to taking up precious storage space, these indexes also required maintenance – defragmentation, statistics updating – and also utilized disk IO whenever the source table was modified. What about underutilized indexes? I don’t know… yet. I plan on examining the index utilization further to make sure that we’re using the most effective indexing strategies and I will probably be dumping indexes that are only used rarely. This is where things get tricky: when should I drop an index? How little usage is the same as no usage? Can I merge this index with another index? How much space is the unused index taking up? I’m curious: Do other DBAs have a usage threshold they consider before they drop an index?