Automatic Missing Indexes Creation Statements

GabrieleTronchin

4.73/5 (9 votes)

30 Mar 2024Public Domain2 min read

24.9K

Automatic creation of non clustered indexes using system SQL entities

Introduction

A common issue about the indexes is to choose which indexes are necessary to create on your database to guarantee a good performance. On this tip, I want to present my solution about this problem, in this case I base all my code on a SQL Server system view named: dm_db_missing_index_details.

Background

Before we proceed with the implementation, maybe it's useful to know something more about dm_db_missing_index_details.

dm_db_missing_index_details returns detailed information about missing indexes. In this tip, we are mostly interested in these columns:

index_handle: It's an unique identifier across the server and identifies a particular missing index.
equality_columns: contains all columns used for equality predicates
inequality_columns: contains all columns used for other comparison
included columns: columns that are necessary to include on the index as covering columns for the query.
statement: complete table name where the index is missing.

Implementation

The implementation of this system is based on three entities:

A simple function that calculates index's name that we want create
A user view to simplify dm_db_missing_index_details
A procedure to create a statement for each index

I chose to divide this system on three procedures, but honestly it's possible merge the stored procedure and view. I personally didn't choose this solution because I want check from my business logic about what kind of indexes there are before creating them.

Using the Code

1. Function - fn_Index_CreateIndexName

In this function, there are three input parameters:

@equality_columns
@equality_columns
<a>@index_handlE</a>

The function's target is to create a unique name for each index that we want to create.
So first of all, we concatenate @equality_columns and @equality_columns input parameter, after that if result name is greater than 120 characters, it will cut at 120.

Why 120?

Because the maximum length of a name in SQL Server is 128 characters. After that function adds at the end of the name @index_handlE, that it's only to guarantee an unique index name.

CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000),
@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)
AS
BEGIN
	
	DECLARE @IndexName NVARCHAR(255)

	SET @IndexName = ISNULL(@equality_columns,@Inequality_columns)

	SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_'))

	SET @IndexName = RTRIM(REPLACE(@IndexName,']','_'))

	SET @IndexName = REPLACE(@IndexName,',','')

	SET @IndexName = REPLACE(@IndexName,'_ _','_')

	IF LEN(@IndexName) > 120
	BEGIN

		SET @IndexName = SUBSTRING(@IndexName,0,120)

	END  

	SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15))
	 
	RETURN @IndexName 
END

2. View - vw_Index_MissingIndex

This view is based on dm_db_missing_index_details joined with the table sys.databases and use the function fn_Index_CreateIndexName to calculate the missing index names.

CREATE VIEW [dbo].[vw_Index_MissingIndex]
AS

SELECT  '[' + d.name + ']' as DBName,
        [dbo].[fn_Index_CreateIndexName]
        (mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID,
        REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns,
        REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns,
        mid.Included_columns,
        mid.[statement]
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.databases d
on d.database_id = mid.database_id

3. Stored Procedure - usp_Index_MissingIndexCreationStatements

This procedure is based on vw_Index_MissingIndex and results in indexes creation statement.

CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
AS

DECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)
DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)

-- PREPARE PLACEHOLDER

SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS(SELECT * 
FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )
				BEGIN
				CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}'

SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
				END;' + char(13) + char(10)

-- STATEMENT CREATION
 
SELECT
	DBName,
	CASE
	WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
				REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', 
				mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
				+ '
				   ( ' +
				   COALESCE(mid.equality_columns,'') +
				   ' ASC,' + 
				   COALESCE(mid.Inequality_columns,'') +
				   ' ASC
				)' +
				COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
				+ @IndexCreationPlaceholder_End

		WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
				REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,
				'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
				+ '
				   ( ' +
				   COALESCE(mid.Inequality_columns,'') +
				   ' ASC
				) ' +
				COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
				+ @IndexCreationPlaceholder_End
				
		WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
				REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}',
				mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
				+ '
				   ( ' +
			   COALESCE(mid.equality_columns,'') +  ' ASC
					) ' +
				COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
				+ @IndexCreationPlaceholder_End
		ELSE NULL
	END AS Index_Creation_Statement,
	' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement]  
	+  + char(13) + char(10) AS Index_Drop_Statement
FROM [dbo].[vw_Index_MissingIndex] AS mid

Complete Code

-- CREATE FUNCTION fn_Index_CreateIndexName

CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _
@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)
AS
BEGIN
	
	DECLARE @IndexName NVARCHAR(MAX)

	SET @IndexName = ISNULL(@equality_columns,@Inequality_columns)

	SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_'))

	SET @IndexName = RTRIM(REPLACE(@IndexName,']','_'))

	SET @IndexName = REPLACE(@IndexName,',','')

	SET @IndexName = REPLACE(@IndexName,'_ _','_')

	IF LEN(@IndexName) > 120
	BEGIN

		SET @IndexName = SUBSTRING(@IndexName,0,120)

	END  

	SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15))
	 
	RETURN @IndexName 
END

GO

-- CREATE FUNCTION vw_Index_MissingIndex

CREATE VIEW [dbo].[vw_Index_MissingIndex] 
AS

SELECT	'[' + d.name + ']' as DBName,
		[dbo].[fn_Index_CreateIndexName]
		(mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID,
		REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns,
		REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns,
		mid.Included_columns,
		mid.[statement]
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.databases d
on d.database_id = mid.database_id

GO

CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
AS

DECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)
DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)

-- PREPARE PLACEHOLDER

SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS_
(SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )
				BEGIN
				CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}'

SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, _
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
				END;' + char(13) + char(10)

-- STATEMENT CREATION
 
SELECT
	DBName,
	CASE
	WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
				REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', 
				mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
				+ '
				   ( ' +
				   COALESCE(mid.equality_columns,'') +
				   ' ASC,' + 
				   COALESCE(mid.Inequality_columns,'') +
				   ' ASC
				)' +
				COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
				+ @IndexCreationPlaceholder_End

		WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
				REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', 
				mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
				+ '
				   ( ' +
				   COALESCE(mid.Inequality_columns,'') +
				   ' ASC
				) ' +
				COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
				+ @IndexCreationPlaceholder_End
				
		WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
				REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}',
				mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
				+ '
				   ( ' +
			   COALESCE(mid.equality_columns,'') +  ' ASC
					) ' +
				COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
				+ @IndexCreationPlaceholder_End
		ELSE NULL
	END AS Index_Creation_Statement,
	' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement] 
	+  + char(13) + char(10) AS Index_Drop_Statement
FROM [dbo].[vw_Index_MissingIndex] AS mid

GO

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication