Index Question - Determining When An Index Is More Overhead Then Benefit

Question

Index Question - Determining When An Index Is More Overhead Then Benefit

YSLGuru

SSC-Insane

Points: 21661
More actions
April 6, 2010 at 11:22 am

#213483

I've been tracking and storing the Index Stats info returned by SYS.DM_DB_INDEX_USAGE_STATS for several weeks now. We restart our SQL Server each day so the values returned by SYS.DM_DB_INDEX_USAGE_STATS reset each day and this is why I'm storing these each day. I 'm doing this to see the index usage of all the indexes over a monthly cycle.
While I haven't gotten a full months worth of data yet I do have enough days worth to see some trends. I'm looking for input from your SQL Gurus out there as to whether or not you'd agree with the below assessment on Index maintenance.
If I have an Index that over a full cycle, has a large value under the User_Updates columns but has a value of 0 for all 3 of the other counts (User_Seeks, User_Scans, User_Lookups) , is it accurate to say that column is pure overhead as it's not being used ever and is only been accessed when and Update Delete or Insert has been made and is there updated to reflect this change in the data?
I think this is a fairly straightforward yes but in the event I'm missing something I'd like to get some feedback from others on this. OUr software vendor has asked us to drop a couple of custom indexes from our DB that their application uses however I don't want to because those custom indexes are getting actual/real world use where as up to %25 - %30 of the stock indexes provided by the vendor as part of the DB setup are not getting any activity other then Updates.
Thoughts?? Comments?
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

For the period you're covering, the answer is yes. That still doesn't say that the day before you started or the day after you stop, some query won't come along and need that index, but that's the chance you take when you start dropping objects.

"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

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

Grant Fritchey (4/6/2010)
For the period you're covering, the answer is yes. That still doesn't say that the day before you started or the day after you stop, some query won't come along and need that index, but that's the chance you take when you start dropping objects.

This is why I'm aggregating the reulst of this thing over 30 days before making any real decissions on what to drop and what to keep. In our business everything that is done (more then once a year) is done at some point in the 30 day cycle so if after 30 days teh index isn't getting any real use my thoughst are its just overhead.

Thanks for replying

Kindest Regards,

Just say No to Facebook!

Paul White SSC Guru Points: 150468 More actions · Answer 3

YSLGuru (4/6/2010)
If I have an Index that over a full cycle, has a large value under the User_Updates columns but has a value of 0 for all 3 of the other counts (User_Seeks, User_Scans, User_Lookups) , is it accurate to say that column is pure overhead as it's not being used ever and is only been accessed when and Update Delete or Insert has been made and is there updated to reflect this change in the data?
I think this is a fairly straightforward yes but in the event I'm missing something I'd like to get some feedback from others on this.

Be careful to check the following:

The index might be referenced by name in WITH(INDEX()) hints

column1

column1, column2

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

Paul White NZ (4/6/2010)
YSLGuru (4/6/2010)
If I have an Index that over a full cycle, has a large value under the User_Updates columns but has a value of 0 for all 3 of the other counts (User_Seeks, User_Scans, User_Lookups) , is it accurate to say that column is pure overhead as it's not being used ever and is only been accessed when and Update Delete or Insert has been made and is there updated to reflect this change in the data?
I think this is a fairly straightforward yes but in the event I'm missing something I'd like to get some feedback from others on this.
Be careful to check the following:
The index might be referenced by name in WITH(INDEX()) hints
A UNIQUE index might be used by the optimizer to infer conditions that allow it to produce an efficient plan. The index will not show as being used, but dropping it could change query plans in unpredictable ways - a very subtle side-effect. For example, a UNIQUE INDEX on column1 can be used by the optimizer to infer than a (non-unique) index on (column1, column2) is also UNIQUE.
Paul

Good point, thanks

Kindest Regards,

Just say No to Facebook!

jrc172 SSC Enthusiast Points: 162 More actions · Answer 5

Also determine that any UNIQUE indexes are not used by the application to avoid duplicates. Dropping the index could potentially allow duplicate values in the column(s) with less than optimal effects.