Index Usage
-
Hi,
I have some indexes on a table that have high user seeks and user updates but no user scans. Does this mean the indexes are not effective?
-
Seeks show efficient use of the index.
The optimiser would rather do a scan of a clustered instead of a scan on an index if it thinks a lot of rows will be returned and all the columns it needs aren't on the index as it would have to do key lookups on the results.
If you can find the queries doing the scans of the primary key, that you think should be using the non-clustered indexes, then you might be able to make them use the indexes if you include some more columns on the indexes.
-
Seeks are great! Don't complain.
However, they're only great when returning smaller sets of filtered data. For very large data sets or no filtering, scans are better. You should see a little of both on any index. However, seeks are nothing to be concerned about in general and, in fact, should be celebrated most of the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I'll have to say "oh, be careful now" and yell "It Depends"!
The presence of only "Seeks" in the usage stats must not be interpreted as something meaning "everything is great". It could be and frequently is and indication of something very bad. For example, if you have a large table and your queries are using, say, a 100,000 individual seeks for the query instead of a seek to the first row of a range followed by a range scan of the clustered index, then everything is NOT "OK". In fact, you have a really serious resource usage problem that will also cause a serious duration problem.
Likewise, having nothing but scans on a given index doesn't necessarily mean that you have bad code. A scan of a given index may, in fact, be much more effective than seeks.
The only fact that you should derive from sys.dm_dm_index_usage_stats is based exactly on the usage implied by the DMV and this is, is the index being used. I'll also state that if there is no usage, it doesn't actually mean that the index isn't being used. It could be there just to enforce uniqueness and it could also be that its presence is providing valuable statistics to the optimizer to try something else.
The ONLY way to evaluate the usefulness of an index is to evaluate the code that uses it or consumes the statistics.
A great example may be found in the form of indexes on audit and other history tables. They may go for months showing absolutely zero usage but the indexes may be enforcing uniqueness of inserts and the indexes will certainly be needed when an audit requirement comes up.
The usage stats certainly do have their use but using them to accurately evaluate if the code that's using indexes is good or not isn't one of them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OK thanks for your comments.
So I need to review the queries using the indexes, would this mean that the index are being used or not efficiently ?
-
Pretty much yes. The index usage stats are just one piece of information. However, it's not dispositive information. It's a part of the whole puzzle. The whole puzzle very much is about the code, the statistics, the execution plans and the overall behavior of the system. No single one number in any of this tells you that something is good or bad.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply