1

I have an SQL instance that is using version 2008R2 (upgrade is not an option at this moment) and is connected to a transactional application. The data size on the whole instance is about 1TB which is spread across more than 100 DBs (2 databases consume about ~200GB each, the rest of them are quite small).

I have been asked to verify what can we improve from the SQL side as users are complaining about the performance (it has been degraded over the years). From the very beginning, I notice that there is huge room for improvement but I thought that the quickest win would be index review (I think that no one have a look into them since the app was designed). I wanted to follow the DEATH method from Brean's Ozar curse... but.. in sys.dm_db_index_usage_stats I can see more than 1000 indexes that were not been used since the last startup (~3 weeks)... It is simply not possible to review them one by one.

In such a situation... is there any reason why I should not just disable or drop all of them (I mean these with 0 seeks, 0 scans 0 lookups)? The longer I think about that more convinced I am that there is no point in reviewing them, but maybe I am missing something.

2 Answers 2

1

Three weeks is not a very long time to go off of.

Typically indexes with low or no read usage, but are incurring writes, are good candidates to be dropped (aka the D in the D.E.A.T.H. method). But in this case with only three weeks of uptime data to go off of, I'd recommended waiting a little bit longer before making such an analysis.

My reasoning for waiting is that some indexes are used for monthly or even quarterly reports. While they might not be used as often as other indexes, if the queries of those reports run heavy without those indexes, then you can create additional performance issues by dropping them.

In any case, disabling the indexes before choosing to drop them, is a safer approach when determining unneeded indexes. It's much easier to re-enable them then to recreate them.


Aside from all of that, if you can identify a few of the most problematic queries (especially if they're common across each database) then you may want to even attempt to directly tune the problems of those queries. sp_BlitzCache can assist you in finding the most egregious queries.

5
  • if the index is used once per month... is it really a scenario where such index may be worth of keeping? Commented Jul 13, 2022 at 10:12
  • 1
    @RadekGąska If it greatly improves an important monthly task like "billing" or "statement generation". But perhaps such an index can be created when needed and then dropped. Nothing is free - alternatives need to be evaluated in proper perspective.
    – SMor
    Commented Jul 13, 2022 at 12:24
  • 1
    And let's not forget semi-annual and annual processes too!
    – SMor
    Commented Jul 13, 2022 at 12:25
  • 1
    @RadekGąska It depends on the situation, but yes there are times when it's worth keeping because the benefit of it outweighs the upkeep of it. (E.g. if the data that's indexed only changes a few times a month, then that's not going to be the source of your server's performance issues even if that index is only used once every quarter.) And conversely, again, such monthly or quarterly process might bring down your server without that index, on the days it does run.
    – J.D.
    Commented Jul 13, 2022 at 12:33
  • 1
    Ok, thanks. That's totally makes sense. So... what is yours suggestion regarding action plan? Waiting a year to gather whole cycle does not seem to be an option.... Commented Jul 13, 2022 at 12:49
0

It may be some jobs and ETL packages exist in your database. They may are executed in a specific period such as monthly, seasonal, and yearly depending on your business, and used indexes that havn't been used recently. I suggest that don't delete or disable these indexes because they may have a harmful effect on your database performance and daily processes.

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.