缺失索引自动创建语句

缺失索引自动创建语句

引言

一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的所有代码都基于名为 dm_db_missing_index_details 的 SQL Server 系统视图。

背景

在开始安装前，进一步了解 dm_db_missing_index_details 会更有益处。

dm_db_missing_index_details 会返回缺失索引的细节信息。在本文中，我们将更关注以下几列：

index_handle：它是一个独特的跨服务器标识符，并且标志一个特定的缺失索引。equality_columns：包含用于相等谓词的所有列inequality_columns:包含用于其他比较的所有列included columns索引中所包含的查询必要出现列statement: 补充完整索引缺失的表名

实现

本系统的实现基于以下三个实体：

笔者选择将这个系统分为三段进程，但实际上合并存储过程和视图也是可行的。笔者之所以没有选择后一种做法是因为想在创建索引之前先从业务逻辑检查一下存在哪些索引。

使用代码

函数 fn_Index_CreateIndexName

在这个函数中，有三个输入参数：

1.  @equality_columns2.  @equality_columns3.  @index_handlE

该函数的目的是为每个期望创建的索引都创建一个唯一名称。

因此，首先拼接@equality_columns和@equality_columns两个输入变量，如果拼接后所得结果超过120个字符，那就截取至第120个字符。

为什么是120个字符？

因为在SQL Server中，命名最大长度为128个字符。这个函数在 @index_handlE 名字结尾添加字段以便保证唯一的索引名。

CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)ASBEGINDECLARE @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) > 120BEGIN    SET @IndexName = SUBSTRING(@IndexName,0,120)END  SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15))RETURN @IndexName END

视图  vw_Index_MissingIndex

该视图基于dm_db_missing_index_details和 sys.databases 表，并使用fn_Index_CreateIndexName 函数来计算缺失的索引名。

CREATE VIEW [dbo].[vw_Index_MissingIndex]ASSELECT  '[' + 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 midINNER JOIN sys.databases don d.database_id = mid.database_id

存储过程  usp_Index_MissingIndexCreationStatements

该存储过程基于 vw_Index_MissingIndex，并且输出索引创建语句。

CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]ASDECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)-- PREPARE PLACEHOLDERSET @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 CREATIONSELECT    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 NULLEND AS Index_Creation_Statement,    ' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement]  _    +  + char(13) + char(10) AS Index_Drop_StatementFROM [dbo].[vw_Index_MissingIndex] AS mid

完整代码

-- CREATE FUNCTION fn_Index_CreateIndexNameCREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)ASBEGIN        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 ENDGO-- CREATE FUNCTION vw_Index_MissingIndexCREATE VIEW [dbo].[vw_Index_MissingIndex] ASSELECT  '[' + 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 midINNER JOIN sys.databases don d.database_id = mid.database_idGOCREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]ASDECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)-- PREPARE PLACEHOLDERSET @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 CREATIONSELECT    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 NULLEND AS Index_Creation_Statement,' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement]  _    +  + char(13) + char(10) AS Index_Drop_StatementFROM [dbo].[vw_Index_MissingIndex] AS midGO