2

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.

2 Answers 2

4

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

  1. PROBLEM SOLVED
0
3

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.

2
  • 1
    If you have a report that runs once a month, it sometimes makes sense to create the index prior to the report running, and then drop it afterwards. Especially with columnstore, which can cause awkward situations on transactional tables, when there's no sense in the thing being there the rest of the month. Dec 1, 2021 at 13:28
  • @ErikDarling Oh, I absolutely agree, that certainly would be the best solution to some use cases. But there's also use cases, such as what my example meant to demonstrate, where the locking to create and drop the index (on a larger table) maybe isn't tolerable (to the business) and the overall better solution is to "set it and forget it" - that is create the index initially on the table, and incur the small maintenance cost on every write. Even with online indexing in Enterprise edition, resource contention is still a factor, so it's really variable on each use case. But good suggestion.
    – J.D.
    Dec 1, 2021 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.