When to keep an index - Analysing index usage stastics in SQL Server 2008

Question

I'm doing an index report on my MS SQL 2008 database (Right click database -> Reports -> Index Usage Statistics)

It tells me that one of my indexes uses: 88 user seeks 0 user scans 6,134,141 user updates

Can someone explain to me:

1. What the difference between user seeks and user scans are?
2. How should I determine when to keep an index or drop it depending on the user seeks + user scans vs user updates?

I think in this case the cost of maintaining the index is not worth it.

Answer 1

1. Here is a good article that goes over seeks and scans (and indexes in general). It will probably do a better job than any SO post.
2. It can be a bit of an art form determining whether you need an index or not. If those 88 seeks take absolutely essential reporting queries from a runtime of 3 hours down to 30 seconds then keep them. I think the first step would be to figure out which queries are using them, how much the indexes help those queries and how important those queries are.

Snipit from the article (definitely give it a read though):

Scans

An index scan is a complete read of all of the leaf pages in the index. When an index scan is done on the clustered index, it’s a table scan in all but name.

When an index scan is done by the query processor, it is always a full read of all of the leaf pages in the index, regardless of whether all of the rows are returned. It is never a partial scan.

A scan does not only involve reading the leaf levels of the index, the higher level pages are also read as part of the index scan.

Seeks

An index seek is an operation where SQL uses the b-tree structure to locate either a specific value or the beginning of a range of value. For an index seek to be possible, there must be a SARGable3 predicate specified in the query and a matching (or partially matching) index. A matching index is one where the query predicate used a left-based subset of the index columns. This will be examined in much greater detail in a part 3 of this series.

The seek operation is evaluated starting at the root page. Using the rows in the root page, the query processor will locate which page in the next lower level of the index contains the 1st row that is being searched for. It will then read that page. If that is the leaf level of the index, the seek ends there. If it is not the leaf then the query processor again identifies which page in the next lower level contains the specified value. This process continues until the leaf level is reached.

Once the query processor has located the leaf page containing either the specified key value or the beginning of the specified range of key values then it reads along the leaf pages until all rows that match the predicate have been returned.

Answer 2

One important point to note up front: the index usage statistics are reset every time the database is started. So, it's hard to evaluate your 88 seeks without knowing when you last restarted. 88 seeks in the last hour is quite different than 88 seeks in the last month.

1. A user seek is looking for a particular row or set of rows in the index that match the criteria of your query. A user scan is reading all rows in the index. For obvious reasons, seek operations are preferable over scan operations.
2. I'm not aware of any general guidelines that say "when the seek/update ratio is X, drop the index." Look at your index in terms of these General Design Guidelines and benchmark before and after performance of your queries to determine the impact of dropping the index.