SQL Server Missing Index DMV

sql server

Did you know that your SQL Server is keeping track of the indexes that it thinks you should create?  The “missing index” DMVs in SQL Server are a great feature in SQL Server 2005 and later.  If you want to see if this feature can spare you the tedium of an afternoon identifying poor performing queries and tuning them, all you have to do is ask:

SELECT migs.avg_total_user_cost * 
       (migs.avg_user_impact / 100.0) * 
       (migs.user_seeks + migs.user_scans) AS improvement_measure,
       'CREATE INDEX [missing_index_' + 
                      CONVERT (varchar, mig.index_group_handle) + 
                      '_' + CONVERT (varchar,mid.index_handle) + 
                      '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'+ 
                      ' ON ' + mid.statement + 
                      ' (' + ISNULL (mid.equality_columns,'') + CASE 
                                         WHEN mid.equality_columns IS NOT NULL 
                                         AND mid.inequality_columns IS NOT NULL 
                                         THEN ',' ELSE ''END + 
                      ISNULL (mid.inequality_columns, '') + 
                      ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') 
                      AS create_index_statement,
        migs.*, 
        mid.database_id, 
        mid.[object_id]
FROM sys.dm_db_missing_index_groups mig INNER JOIN 
     sys.dm_db_missing_index_group_stats migs 
     ON migs.group_handle = mig.index_group_handle INNER JOIN 
     sys.dm_db_missing_index_details mid 
     ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * 
      (migs.avg_user_impact / 100.0) * 
      (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * 
         migs.avg_user_impact * 
         (migs.user_seeks + migs.user_scans) DESC

You’ll want to run this after your server has been up and running a normal workload for a while.  If this returns no results, that’s good news and indicates that you’re not missing any indexes that are obvious enough for the DMV to detect.  If it does return some suggestions, even better: you just got a clue on what you could do that might improve your server performance.

Advertisements

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