Cool Tips‎ > ‎Indexes‎ > ‎

Indexes - Identify Top N Missing Indexes

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author: Unknown/ChillyDBA
Date:    14 Nov 2011/12 Jul 2012

Description

Uses system DMVs to create a weighted list of missing indexes.
The list includes the key columns that could have been used in both equality and inequality  predicates plus those that would have been useful as included columns.
Information about how many times Index seek and scan operations would have utilized the indexes is also included.
The weighting is a combination of seeks + scans modified by cost and user impact figures.

The results are only recommendations.  Each should be validated against application need and SQL Server resource usage (plus a pinch of common sense), as sometimes the recommendations can produce multiple near-identical recommendations.

Code

Function:


DROP FUNCTION dbo.udf_GetTopNMissingIndexes
GO

CREATE FUNCTION dbo.udf_GetTopNMissingIndexes
                                      
(
                                          
@n INT
                                      
)
RETURNS @TopNMissingIndexes TABLE
  
(
      
DatabaseName            SYSNAME NULL,
      
TableName               SYSNAME NULL,
      
EqualityColumns         VARCHAR(1000),
      
InequalityColumns       VARCHAR(1000),
      
IncludedColumns         VARCHAR(1000),
      
UserSeeks               INT,
      
UserScans               INT,
      
LastUserSeek            DATETIME,
      
AvgUserImpact           DECIMAL(18,2),
      
AvgTotalUserCost        DECIMAL(18,2),
      
WeightedCost            DECIMAL(18,2),
      
IndexHandle             INT
  
)
AS
/***************************************************************
Purpose:   To retrieve the top N missing indexes.
           These are stats created by the DBMS when queries are parsed and indexes are
           considered to be a more optimal cost solution but which are missing

           NOTE:  These are system-generated recommendations and should not be implmented
           without careful consideration
          
           The following DMVs are used:
               sys.dm_db_missing_index_group_stats     - summary missing index information (#usage and cost)
               sys.dm_db_missing_index_groups          - link between summary and detail
               sys.dm_db_missing_index_details         - detailed missing index information (columns)
          
          
Author:        Unknown
History:   14 Nov 2011 - Initial Issue
           12 Jul 2012 - ChillyDBA - Converted to a table valued function
            with optional number or results (@N)

****************************************************************/

BEGIN

   INSERT
@TopNMissingIndexes
      
(
        
DatabaseName,
        
TableName,
        
EqualityColumns,
        
InequalityColumns,
        
IncludedColumns,
        
UserSeeks,
        
UserScans,
        
LastUserSeek,
        
AvgUserImpact,
        
AvgTotalUserCost,
        
WeightedCost,
        
IndexHandle
      
)
      
    
SELECT TOP(@n)
        
SUBSTRING(mid.statement, 1, CHARINDEX('.', mid.statement) - 1)                       AS DatabaseName,
        
SUBSTRING(mid.statement, CHARINDEX('.', mid.statement), DATALENGTH(mid.Statement)) AS TableName,
        
mid.equality_columns                                                                    AS EqualityColumns,
        
mid.inequality_columns                                                              AS InequalityColumns,
        
mid.included_columns                                                                    AS IncludedColumns,
        
migs.user_seeks                                                                     AS UserSeeks,
        
migs.user_scans                                                                     AS UserScans,
        
migs.last_user_seek                                                                 AS LastUserSeek,
        
migs.avg_user_impact                                                                    AS AvgUserImpact,
        
avg_total_user_cost                                                                 AS AvgTotalUserCost,
        
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)                     AS WeightedCost,
        
mid.Index_Handle                                                                        AS IndexHandle
  
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)
  
ORDER BY
      
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;
      
  
RETURN
END


Test Code:


SELECT * FROM dbo.udf_GetTopNMissingIndexes (20)
SELECT * FROM dbo.udf_GetTopNMissingIndexes (50)

ċ
udf_GetTopNMissingIndexes.sql
(3k)
Andy Hughes,
Jul 12, 2012, 8:24 AM
Comments