Useful Tricks for Finding Missing and Bad Indexes in SQL Server 2005

As I have written before, having proper indexes in place to support your workload is extremely critical with SQL Server 2005 (as it is with any relational database). Generally speaking, you will want more indexes with a reporting or DSS workload, and fewer indexes with an OLTP workload. Regardless of your workload type, you should be aware of whether your indexes are being used and whether you are missing any indexes that would be useful for SQL Server.

In the dark ages before SQL Server 2005, it was pretty difficult to discover this critical information, but with Dynamic Management Views (DMV), you can easily discover what is going on with your indexes. You can find indexes that are not being used and you can find missing indexes. As Rico Mariani says, "If you are not measuring, you are not engineering".

The DMV queries that you see below will tell you this information. If you see an index that has millions of writes, with zero or very few reads, then that means that you are getting very little benefit from maintaining the index, and you should consider dropping that index. Of course, as the number of reads goes up, it becomes more of a judgement call. That is why being familiar with your workload is very important.

One caveat with the "missing index" query is that it will return results that were triggered by ad-hoc queries and maintenance job related work that can make it harder to interpret.  You always want to look at the last_user_seek and the user_seeks columns to see the last time and how often SQL Server thinks it wants the index that it thinks is "missing". If you see a row with a high index advantage with a last_user_seek from a few seconds or minutes ago, it is probably from your regular workload, so you probably want to strongly consider adding that index.

One trick/bug I have discovered recently is that if you add a new index of any sort to a table, it will clear out all of the missing index stats for that table. This may lead you to believe that there are no more missing indexes on the table, which is probably not true. Just wait it little while, and then run the query again.

 

-- Possible bad Indexes (writes > reads)
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,
           'Total Writes' =  user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,
            'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
    FROM sys.dm_db_index_usage_stats AS s 
    INNER JOIN sys.indexes AS i
    ON s.object_id = i.object_id
    AND i.index_id = s.index_id
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = @dbid
    AND user_updates > (user_seeks + user_scans + user_lookups)
    ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;

    

--- Index Read/Write stats for a single table
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id,
           reads = user_seeks + user_scans + user_lookups, writes =  user_updates
    FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.object_id = i.object_id
    AND i.index_id = s.index_id
    AND s.database_id = @dbid
    AND object_name(s.object_id) IN( 'tablename')
    ORDER BY object_name(s.object_id), writes DESC, reads DESC;
    
-- Show existing indexes for this table
    EXEC sp_HelpIndex 'tablename'
 
-- Missing Indexes 
    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    ORDER BY index_advantage DESC;

-- Missing indexes for a single table
    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    WHERE statement = '[databasename].[dbo].[tablename]' -- Specify one table
    ORDER BY index_advantage DESC;

-- Examine current indexes
    EXEC sp_HelpIndex 'dbo.tablename'
 
Technorati Tags: ,

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

72 Responses to Useful Tricks for Finding Missing and Bad Indexes in SQL Server 2005

  1. Joe says:

    Is there a way to find the statement associated with the generation of the missing index entry? Would hundreds of objects it would be nice to be able to identify the specific statement.

  2. Jason says:

    At http://www.progiftstore.com/ you can get the best quality swiss replica watches with the lowest price, in progiftstore, you can buy replica watches with a great discount.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s