|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:
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
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?