Index usage statistics

December 15th, 2011 - 08:27 am ET by simon | Report spam

I have indexes with the following statistic of usage:

INDEX_NAME/USER_SEEKS/USER_SCANS/USER_LOOKUPS/USER_UPDATES

index1/402725/14952/118868/127681
index2/59857/187/0/38000
index3/2325/456/0/112947
index4/3053/12930/0/112855
index5/55608/31/0/112947
index6/250/0/0/38000
index7/13082/7917/0/112947
index8/42352/116/0/38000
index9/157/0/0/36155
index10/21/129/0/38000
index11/229/2/0/36155

If I look only this info, than I should delete index6, index9, index10
and index11. They have a lot of user updates and small number of
usage.
The worst usage in practice is "key lookup". It goes to leaf level of
clustered index (with clustered index seek) to get additional data,
that don't exist in nonclustered index.
So it is not that bad at all - I guess, it is better nonclustered
index seek( or scan if small index) + key lookup, than table
scan(clustered index scan) on the other side, which would happen if I
delete the index which is used for key lookup?

If index has a lot of updates, but the index is small, than this is
not necessarily that it is so costly to maintain.
Because on the other hand, if you have a lot of wide columns on table
and it is without an index, the table scan would happen and it would
mean slow performance.
So, I should find out which query uses which index - where can I see
that? And than decided if a lot of updates still have common sense
even if the usage of index is small.
Maybe the usage happens on most important queries.

But in most cases I should delete the indexes with greater value of
updates than usages? What is the best practice here?

Thank you,
Simon

Follow the discussion Replies

3 replies

Make a reply

Replies

#1 Erland Sommarskog

December 15th, 2011 - 03:48 pm ET | Report spam

simon () writes:

If I look only this info, than I should delete index6, index9, index10
and index11. They have a lot of user updates and small number of
usage.

But keep in mind that the DMV is cleared when the server is restarted,
so it could be that those queries that need these indexes have not
executed yet.

The worst usage in practice is "key lookup". It goes to leaf level of
clustered index (with clustered index seek) to get additional data,
that don't exist in nonclustered index.
So it is not that bad at all - I guess, it is better nonclustered
index seek( or scan if small index) + key lookup, than table
scan(clustered index scan) on the other side, which would happen if I
delete the index which is used for key lookup?

But that's index_id = 1, isn't it? Index 1 is the clustered index. I did
a quick test, and it seems that user_lookups is only populated for the
clustered index. Thus, there does not seem to be a away to distinguish
seeks without lookups from those with.

Whether index seek + key lookup is better than table/CI scan depends on
the hit rate. To read just a handful rows, seek + lookup is better.
But if you want to read the entire table, it's a disaster.

So, I should find out which query uses which index - where can I see
that?

In the plan cache. That is, you can write a crazy amount of Xquery to
analyse the plans in the cache to find the queries that uses a certain
index. But of course, if there are plans that fallen out of the cache,
you want find them.

And, no, before you ask, I don't have any such queries canned.

But in most cases I should delete the indexes with greater value of
updates than usages? What is the best practice here?

I don't think there is any straight answer here. Maybe there is an
index that is only needed for a single report. But that's the report
the managing directors runs five minutes before the board meeting.
Of course, the more you know about your tables and your data, the
better decisions you can make.

Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx

Read all the answers

Index usage statistics

Replies

Similar topics