March 10, 2010 at 8:51 am
If an index is being used more (or better yet, much more) than it is being updated, then it's needed, and if it's not being used at all then it should be dropped. What about the in-between? Is there a rule of thumb for determining whether an index is getting used too little and updated too much to bother keeping?
Now that we can measure user_scans + user_seeks + user_lookups and compare that to user_updates via DMVs, we can tell whether an index is being used more or less than it's being updated. Is this a useful starting measure of an index's effectiveness? If an index is being used only 10% or 20% as often as it's being updated, does that mean it should be removed? What other factors would come into play? Is there an algorithm to encapsulate those?
March 10, 2010 at 9:07 am
I've used those numbers as a starting point, but you really need to identify where that seldom used index is actually being used. It could be a very important person running a report on demand - tho not very often - that if you dropped the index it could cause the report to take hours to generate rather then seconds.
IOW, I think it demands more investigation into where/how the index is being used - not just how many times it was used. Then the trade-offs begin with the other effects of keeping the index...
jg
March 10, 2010 at 9:14 am
I would agree, if you are not the developer that uses the database, you may not understand why that 1 index is there and is only used every 3 months.
Brent Ozar did a presentation for SQLBits conference that is really good and has the queries he uses. http://www.brentozar.com/sql-server-training-videos/t-sql-performance-tuning-for-race-car-drivers/[/url]
He makes a good point in the video that if you have say 2 indexes that only 1 is heavily used it may be more efficient to just combine those indexes into 1. It all depends on your environment and it would be best for you to gather the information and then discuss with the developers.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 11, 2010 at 6:44 am
Melton (3/10/2010)
http://www.brentozar.com/sql-server-training-videos/t-sql-performance-tuning-for-race-car-drivers/[/url]
nice video
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 13, 2010 at 2:06 am
Prince Ali (3/10/2010)
user_scans + user_seeks + user_lookups
Beware the fact that user_lookups are always counted against the clustered index. Non-clustered indexes always have a zero for that counter.
As far as the wider point is concerned, don't be too keen to drop apparently under-used indexes. Be absolutely sure you know which queries use the index, and what the impact will be if you drop it. Indexes are relatively cheap to update, compared to older versions of SQL Server.
Rule of thumb: never drop an index, unless you are fully across every aspect of its use, and are prepared to get paged at 4am to trouble-shoot a problem caused by its non-existence. A particularly fun example would be where some code names the index in a hint.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply