A SQL Server 2005 DMV Cleans Up Your Indexes

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:

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.