Hi All,
Need Index suggestion.
I am seeing on one of our prod table, I see 18 non-clustered indexes created. How can start/determine what indexes can be dropped and what key indicators should be looked at when dropping an index.
Is there any useful script to do the cleanup?
Thanks,
Sam
December 13, 2017 at 1:02 am
First thing I would look at is index usage.
you will need to use dm_db_index_usage_stats for this, and this will show you lookups, reads, writes. A quick search in google should give you some examples of scripts.
Look for indexes that have 0 reads (or close to)but high writes.
Next thing I would look at is page splits, and fill factor. This would be to determine which of the indexes I would need to look at first, i.e. do my indexes writes have the added impact of excessive page splits (bear in mind that every time a new page is added to the index it counts as a page split, so don't take the result at face value). You will need to use dm_db_index_operational_stats for this.
The next thing I would check is index waits, again using dm_db_index_operational_stats. This will show if any indexes are being hit with excessive lock or latch waits.
The above should give you enough detail to look at your 18 nci's and created a prioritised list on which to act on first.
Alternatively, if you are not interested in any of the above, try running Brent Ozar's sp_BlitzIndex. That will give you all the operational stats you need, and you can make a decision.
There is a slight caveat to all this though; remember that when an index is rebuilt operational stats (like reads and page splits) are reset. Also, all stats are reset when the instance is restarted. Try and give yourself a good window (maybe 6-8 weeks?) whereby you can record the index usage.
In terms of clean up, if you are only looking at 18 nci's, I wouldn't bother with a script, just to it manually. You may also want to consider disabling the index in the first instance, instead of dropping. This way, if performance is hit from the change, you can simply rebuild the index to bring it back online, you won't need to go through change control to recreate the index.
The driver has to be the leading edge and then subsequent columns. Also look for ones that are unique and non-unique but have the same columns. Always go for keeping unique indexes where possible. The real problem comes when two indexes are effectively the same, but, they're used in different ways within queries. For example, an index on A with an include of B & C is the same as an index on A with an Include of B, C, D & E. However, the optimizer might choose to scan the first index rather than the second index because it's smaller. So even though these are "duplicate" indexes and you should be able to drop the first because it's covered by the second, you may find some queries degrade because of this.
There is no simple, formulaic approach to eliminating duplicate indexes. It requires testing and validation at each step.
"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
December 13, 2017 at 7:26 am
Great guidelines. Thanks Dim and Grant.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply