| Downloads |
|---|
| 97479.zip |
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, 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.
