How many times has someone asked you, “How
do I go about cleaning up all these unused
indexes?” If you’re like most DBAs, you’ve
often fielded this question or even pondered it yourself. In
SQL Server 2000, cleaning up unused indexes is an almost
impossible task that requires many hours of investigation
and a solid knowledge of the workings of each and every
table in your databases. SQL Server 2005 gives us some
salvation and eases the burden dramatically with the addition
of a Dynamic Management View (DMV) that suits
this task perfectly—that is, the sys.dm_db_index_usage_
stats DMV. Let’s take a brief look at this DMV and put it
to work in your environment.
DMV Basics
Most of the columns that this DMV returns are selfexplanatory,
so I won’t go into the details of all of them.
The columns are essentially broken down into two categories
to individually track user and system access for each
index on each table. The DMV also tracks access to the
table itself through index_id 0 (if it’s a heap) or index_id
1 (if it has a clustered index). Oddly enough, the system
statistics are generated when the indexes are accessed as a
result of internal or system-level operations. These may
consist of (but aren’t limited to) operations such as database
consistency checks (DBCCs), index rebuilds, statistics
updates, and so on. The user statistics are the direct result
of user activity such as Selects, Inserts, Updates, and
Deletes. Some of the key statistics captured for each index
or the heap are as follows:
- Scans—These occur when the access method never
attempts to use the index in a typical B-Tree operation
(e.g., a Seek). In other words, it will read all the pages
in the order it deems appropriate unless there’s a limiting
clause (e.g., TOP, ROWCOUNT).
- Seeks—These occur when the B-Tree or index is used
to fetch one or more rows. This might also include
range scans that start their process with a Seek.
- Lookups—These occur when an index or heap is
accessed via a non-clustered index to retrieve extra
columns not present in the non-clustered index to
satisfy the Select list. These are commonly referred to
as BookMark Lookup operations.
- Updates—These occur whenever there’s an Insert,
Update, or Delete (i.e., not just Updates).
- Last xxx—Tracks the date and time of the most recent
Scan, Seek, Lookup, or Update operation for each of
the user and system statistics.
Check It Out
You can see right away that this DMV offers a host of
possibilities when it comes to diagnosing index and table
usage. However, before you jump in, let me point out a few
important characteristics of this DMV. First, it’s imperative
that you keep in mind the scope of these statistics for
each database. These statistics are reset to 0 each time the database is closed, the databasde is SET OFFLINE, or the
SQL Server instance is restarted. As long as you don’t have
the AutoClose property of the database enabled and you
haven’t manually or programmatically taken the database
offline, these statistics will reflect the activity in that database.
Therefore, before you go deleting indexes based on
these numbers, make sure the database has been up and
actively running for an extended period of time to ensure
that you get a good view of the activity.
Another important factor to consider is that these
statistics don’t take ROLLBACKs into consideration. If
you updated a row and rolled back the transaction, the
associated counters will still be incremented. Triggers that
manipulate data on the underlying table might skew the
numbers that you’re expecting to see in the DMV. For
example, an update on a single row might be reflected as
two updates if the trigger code also updates a row.
If you run the code in Listing 1, page 39, you should
see results similar to those in Figure 1 for the user statistics.
Yes, the figure shows the indexes that are being used, and I
said I would show you how to find the ones that aren’t being
used. Simply running the query in Listing 2 will point out all
indexes and tables that have never been accessed.
Go Further!
I don’t have room here to fully cover this topic, so—as
always—be sure to check out the Performance Tuning
and Optimization forum (sqlforums.windowsitpro.com/
web/forum) for further information.
There, I’ll show you some ways to
narrow the topic. But this short
article should get you started on
cleaning up those unwanted indexes.