A DMV a Day – Day 11

It is day 11 of 30, of a DMV a Day for the month of April 2010. The DMV for Day 11 is sys.dm_db_index_usage_stats, which is described by BOL as:

Returns counts of different types of index operations and the time each type of operation was last performed. Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

You can find lots of information from sys.dm_db_index_usage_stats, but I will just show one query today. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    -- List unused indexes
    SELECT OBJECT_NAME(i.[object_id]) AS [Table Name], i.name 
    FROM sys.indexes AS i
    INNER JOIN sys.objects AS o
    ON i.[object_id] = o.[object_id]
    WHERE i.index_id 
    NOT IN (SELECT s.index_id 
            FROM sys.dm_db_index_usage_stats AS s 
            WHERE s.[object_id] = i.[object_id] 
            AND i.index_id = s.index_id 
            AND database_id = DB_ID())
    AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC;

This query uses sys.indexes and sys.objects to find tables and indexes in the current database that do not show up in sys.dm_db_index_usage_stats. This means that these indexes have no reads or writes since SQL Server was last started (or since the current database was closed or detached, whichever is shorter). If SQL Server has been running long enough that you have complete, representative workload, then there is a good chance that those indexes (and perhaps tables) are “dead”, meaning they are no longer used by your database.  That means you can potentially drop them, after doing some further investigation.

This entry was posted in SQL Server 2008. Bookmark the permalink.

2 Responses to A DMV a Day – Day 11

  1. Fabrício says:

    Very good, I found many unused indexes.

  2. Pingback: When was the Last Time You Removed an Index Because it was Never Used? | SQL Aloha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s