March 23, 2023 at 7:19 am
Hi All,
We see some tables in Prod are having 20-30 indexes and we wanted to do some cleanup to improve performance of DML queries.
In mid of Feburary month, we have collected index usage stats and we are seeing some indexes are contributing to more updates and not for reads+scans+lookups.
After monthly patching we lost that information.So, wanted to know the best way to capture this unused index information and how to take a call on what indexes to be disabled/dropped.
Please share your thoughts on how to do the cleanup in a proper way.
App team wants to disable those indexes which are not useful from past one month. But since after monthly patching, we lost that index usage information.
if we have to capture that data into a table and if we add capture date as a column, how to do the analysis, is it on day-to-day or do we need to consider the last day before patching window to get proper index analysis done?
Regards,
Sam
March 23, 2023 at 11:40 am
You can use sys.dm_db_index_usage_stats to see the information on how an index is behaving. I assume you're using that. However, as you find, that info resets for a number of reasons, outlined here. Sadly, there isn't a science to this. It's just an art. Collect the data, do some evaluations, keep a copy of the index handy, drop it if you think it's not used, watch for fireworks.
A few points on that. First, all bets are off if it's a unique index. These can be referenced by the optimizer "ah, a unique index, good to know" without being used in execution plans. So be very cautious when dropping these. 20-30 indexes could be excessive, but might not be. It really depends on how they get used, if they get used. Mostly I look at the indexes to see which ones are duplicating effort. For example, a table has an index that has a key on colA and includes colB and colC and it has an index that has a key on colA, colB and colC. You could safely get rid of the index with the include statement, since the second index is effectively identical (plus, being more selective if you actually filter by colB & colC).
However, with all of this, testing and careful data collection are key. You can't simply assume that index is redundant. You have to prove it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2023 at 10:48 pm
Go to the following URL. Read the post and watch the movie.
https://www.brentozar.com/blitzindex/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply