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'