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'