缺失索引自动创建语句
【编者按】 本文主要介绍使用系统 SQL 实体自动创建非聚集(non-clustered)索引。作者为意大利软件工程师 GhostHost(笔名)。本文系 OneAPM 工程师编译呈现，以下为正文。引言一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的所有代码都基于名为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: 补充完整索引缺失的表名
实现本系统的实现基于以下三个实体：
[*]一个可以计算出待创建索引名称的简单函数
[*]一个用于简化 dm_db_missing_index_details的用户视图
[*]一个为每个索引创建声明的进程
笔者选择将这个系统分为三段进程，但实际上合并存储过程和视图也是可行的。笔者之所以没有选择后一种做法是因为想在创建索引之前先从业务逻辑检查一下存在哪些索引。
完整代码
[*]-- CREATE FUNCTION fn_Index_CreateIndexName
CREATE FUNCTION . (@equality_columns NVARCHAR(4000), _
@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)
AS
BEGIN
DECLAR
E @IndexName NVARCHAR(MAX)
SET @IndexName = ISNULL(@equali
ty_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 .
AS
SELECT'[' + d.name + ']' as DBName,
._
(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.
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.databases d
on d.database_id = mid.database_id
GO
CREATE PROCEDURE .
AS
DECLARE @IndexCreationPlaceholder_StartAS NVARCHAR(MAX)
DECLARE @IndexCreationPlaceholder_EndAS NVARCHAR(MAX)
-- PREPARE PLACEHOLDER
SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS_
(SELECT * FROM {2}.sys.indexes WHERE = ''IX_{0}'' )
BEGIN
CREATE NONCLUSTERED INDEX 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
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.),'{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.),'{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.),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.equality_columns,'') +' ASC
) ' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
ELSE NULL
END AS Index_Creation_Statement,
' DROP INDEX ON ' + mid._
++ char(13) + char(10) AS Index_Drop_Statement
FROM . AS mid
GO
OneAPM 助您轻松锁定 .NET 应用性能瓶颈，通过强大的 Trace 记录逐层分析，直至锁定行级问题代码。以用户角度展示系统响应速度，以地域和浏览器维度统计用户使用情况。想阅读更多技术文章，请访问 OneAPM 官方博客。本文转自 OneAPM 官方博客
本文转自 OneAPM 官方博客
原文地址：http://www.codeproject.com/Tips/1079651/Automatic-Missing-Non-Clustered-Creation-Statement
+
页:
[1]