Query to Identify Missing Indexes

The following are the missing index DMVs ( From SQL Server 2005 BOL)

sys.dm_db_missing_index_group_stats = Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.

sys.dm_db_missing_index_groups = Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.

sys.dm_db_missing_index_details = Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.

sys.dm_db_missing_index_columns = Returns information about the database table columns that are missing an index.


statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id


/* ——————————————————————
— Title: FindMissingIndexes
— Description: This query returns indexes that SQL Server 2005
— (and higher) thinks are missing since the last restart. The
— “Impact” column is relative to the time of last restart and how
— bad SQL Server needs the index. 10 million+ is high.
— Changes: Updated to expose full table name. This makes it easier
— to identify which database needs an index. Modified the
— CreateIndexStatement to use the full table path and include the
— equality/inequality columns for easier identifcation.
—————————————————————— */

 [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans), 
 [Table] = [statement],
 [CreateIndexStatement] = ‘CREATE NONCLUSTERED INDEX ix_’
  + sys.objects.name COLLATE DATABASE_DEFAULT
  + ‘_’
  + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”)+ISNULL(mid.inequality_columns,”), ‘[‘, ”), ‘]’,”), ‘, ‘,’_’)
  + ‘ ON ‘
  + [statement]
  + ‘ ( ‘ + IsNull(mid.equality_columns, ”)
  + CASE WHEN mid.inequality_columns IS NULL THEN ” ELSE
   CASE WHEN mid.equality_columns IS NULL THEN ” ELSE ‘,’ END
  + mid.inequality_columns END + ‘ ) ‘
  + CASE WHEN mid.included_columns IS NULL THEN ” ELSE ‘INCLUDE (‘ + mid.included_columns + ‘)’ END
  + ‘;’,
FROM sys.dm_db_missing_index_group_stats AS migs
 INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
 INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
  (SELECT TOP (500) group_handle
  FROM sys.dm_db_missing_index_group_stats WITH (nolock)
  ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)) 
 AND OBJECTPROPERTY(sys.objects.OBJECT_ID, ‘isusertable’) = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC


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

%d bloggers like this: