Index usage statistics

Giganews Newsgroups
Subject: Index usage statistics
Posted by:  simon (zupan.n…@gmail.com)
Date: Thu, 15 Dec 2011

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

Replies