Unused Indexes - How Few Is Too Few?

Question

When looking at seeks, scans and updates for indexes in SQL Server, what's a good rule of thumb for determining whether an index is 'unused enough' to be dropped? Maybe some ratio of updates to seeks + scans? I know there won't be any one right answer to this, just wondering if this DBA audience uses any particular standards for making this choice.

Thanks.

Answer 1

I would suggest this way of thinking instead of a rule of thumb:

1. Are write IOs a problem in your system for that table? (ex: slowness or deadlock) if yes NEXT, if no GOTO 5
2. Is the index required for any report or application in your system? (ex: required for a quick report needed by the CEO) if yes GOTO 4, if no NEXT
3. remove the index then GOTO 1
4. Improve the IO performance of the disk subsystem in this way (in order of ease)
   • Verify best practices (e.g., cluster size)
   • Verify storage configuration (raid or disk type)
   • Check tlog performance (write performance is tied to tlog performance)
   • Move the data file to a faster disk
   • Move the tlog file to a faster disk
   • Review how the application is doing writes

then GOTO 1

5. PROBLEM SOLVED

Answer 2

I think it's hard to design standards around this since it can be pretty variable depending on your system.

For example, if you have a very write-heavy table, that can't afford to be locked for too long, and it's only read from on a quarterly basis by a report that's also fairly heavy. There may be an appropriate index on that table to improve the efficiency of the report such that it minimizes locking on the table while reading from it. That index may only have four seeks on it per year, while also having a lot of updates, but still is worth the performance gain for when that quarterly report runs compared to the overhead of maintaining that index on write.

Erik makes a good point in the comments, that in some cases, my above example is better served by only retaining the index for the duration of the execution of the quarterly report. I.e. to create the index just prior to the report being ran, and then dropping it after the report finishes, such that the index doesn't exist for the majority of the unused time throughout the year, resulting in improved write performance on the table. This is a great solution to my above example for use cases where the resources and time allocation is tolerable to the business each time that quarterly report is ran.

But there may also still be cases where such resources and time allocation breaks the threshold of tolerability (e.g. on a large table that requires a lot of resources to create an index for). Then the tradeoff to incur a smaller performance hit on each write to the table, by only creating the index on it once and leaving it permanently, might be more favorable of a solution.