Cool Tips‎ > ‎Indexes‎ > ‎

Indexes - Generate Commands to Create/Drop Recommended 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:    ChillyDBA
Date:        12 Jul 2012

Description

SP to generate the CREATE and DROP statements for the top 20 missing indexes (utilizes the Get Top N Missing Indexes function) .
The drop statement is included to enable quick backing out of changes should the index not be viable, plus it is useful for documenting the changes.
The SP takes one parameter which is an identifier inserted into the index name.  This allows easy identification/grouping of indexes (especially useful when 'optimizing' 3rd party applications where vendor updates may require the indexes to be backed out first).

No table modification is carried out by this SP.

Code

Stored Procedure:


DROP PROCEDURE dbo.usp_GenerateCreateDropMissingIndexCommands
GO

CREATE PROCEDURE dbo.usp_GenerateCreateDropMissingIndexCommands @AutoGenLabel VARCHAR(20) = ''
AS
/***************************************************************
Purpose:   To generate commands to create and drop  missing Indexes  
           recommended by the DBMS.
           This utilises the dbo.udf_GetTopNMissingIndexes() function
           to retrieve a list of 20 and builds both the CREATE and
           DROP commands
          
           The parameter @AutoGenLabel will be used in the index name along with
           date and index handle to help identify/group autogen indexes)

           No changes are made by this stored procedure
          
           The recommendations generated should be reviewed before running the code
                                          
          
Author:        ChillyDBA
History:   12 Jul 2012

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


;WITH MissingIndexes AS
(
  
SELECT TOP 20
         DatabaseName
,
        
TableName,
        
EqualityColumns,
        
InequalityColumns,
        
IncludedColumns,
        
UserSeeks,
        
UserScans,
        
LastUserSeek,
        
AvgUserImpact,
        
AvgTotalUserCost,
        
WeightedCost,
        
IndexHandle
  
FROM dbo.udf_GetTopNMissingIndexes(20)
)

SELECT
  
'CREATE NONCLUSTERED INDEX '
  
+ QUOTENAME('IX_' + @AutoGenLabel + '_Autogen_'
  
+ REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ' ', '_'), ':', '_')
   +
'_' + CAST(IndexHandle AS VARCHAR(22))
   )
   +
' ON ' + DatabaseName + '.' + TableName + '('
  
+ CASE
      
WHEN EqualityColumns IS NULL THEN InequalityColumns
      
WHEN InequalityColumns IS NULL THEN EqualityColumns
      
ELSE EqualityColumns + ',' + InequalityColumns
  
END
  
+ ')'
  
+ CASE
      
WHEN IncludedColumns IS NOT NULL THEN
      
' INCLUDE ( ' + IncludedColumns + ')'
      
ELSE ''
  
END AS MissingIndexSQL
FROM MissingIndexes

UNION

SELECT
  
'DROP INDEX '
  
+ TableName
  
+ '.'
  
+ ('IX_' + @AutoGenLabel + '_Autogen_'
  
+ REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ' ', '_'), ':', '_')
   +
'_' + CAST(IndexHandle AS VARCHAR(22))
   )
FROM MissingIndexes


Test Code:


EXEC dbo.usp_GenerateCreateDropMissingIndexCommands 'DesperationIndex'


ċ
usp_GenerateCreateDropMissingIndexCommands.sql
(2k)
Andy Hughes,
Jul 12, 2012, 8:29 AM
Comments