Sqlserver自动查询缺失索引及拼出建立索引的语句的脚本

官方文档
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-2017

avg_total_user_cost 可通过组中的索引减少的用户查询的平均成本
avg_user_impact 实现此缺失索引组后，用户查询可能获得的平均百分比收益。 该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降
user_seeks 由可能使用了组中建议索引的用户查询所导致的查找次数
user_scans 由可能使用了组中建议索引的用户查询所导致的扫描次数

(migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) > 100000
平均成本×平均收益×访问次数,如果一个表的索引平均成本×平均收益是3左右，但是自上次查找日期和时间以来该表索引的查找seek次数就达到8万次(还没有包括扫描scan的次数的情况下)，那么这个计算数值还是远远大于10万，这种情况下，这个表的就算已经建立了索引还是会提示继续创建索引，这种情况下就需要在语句中排除该表的这个索引或直接排除该表

SELECT
	mig.index_handle,
	mid.database_id,
	mid.object_id,
	mid.[statement] [object_fullname],
	(migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
	'CREATE INDEX [' AS [create_index_part1],
	'missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) AS [create_index_name],
	']' +
	' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') +
	CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') +
	')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') + '  WITH (' AS [create_index_part2],
	'DATA_COMPRESSION = ROW, ONLINE = ON)' AS [create_index_part3],
	migs.group_handle,
	migs.unique_compiles,
	migs.user_seeks,
	migs.user_scans,
	migs.last_user_seek,
	migs.last_user_scan,
	migs.avg_total_user_cost,
	migs.avg_user_impact,
	migs.system_seeks,
	migs.system_scans,
	migs.last_system_seek,
	migs.last_system_scan,
	migs.avg_total_system_cost,
	migs.avg_system_impact
INTO #CINDEXES
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE ((migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) > 100000)
--and mid.object_id<>1771153355 排除某张表的时候，加上某张表的id就行
ORDER BY (migs.avg_total_user_cost * migs.avg_user_impact) * (migs.user_seeks + migs.user_scans) DESC

--## CHECK IF RECORDS EXIST ##

IF NOT EXISTS (SELECT TOP 1 1 FROM #CINDEXES)
BEGIN
	DROP TABLE #CINDEXES
	
	RETURN
END

--## CHECK IF INDEX ALREADY EXISTS ##

CREATE TABLE #RINDEXES (
	index_handle INT NOT NULL,
	sql_statement NVARCHAR(MAX) NULL
	)

DECLARE @TSQL NVARCHAR(MAX)

DECLARE RunPerIndex CURSOR FOR
SELECT
	index_handle,
	database_id,
	object_id,
	improvement_measure,
	object_fullname
FROM #CINDEXES

DECLARE @Handle INT,
		@DatabaseID INT,
		@ObjectID INT,
		@Impact INT,
		@ObjectFullName SYSNAME,
		@Rebuild BIT,
		@Filegroup SYSNAME,
		@IndexName SYSNAME

OPEN RunPerIndex
FETCH NEXT FROM RunPerIndex INTO @Handle, @DatabaseID, @ObjectID, @Impact, @ObjectFullName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @TSQL =
'SET @Rebuild = 0
SET @Filegroup = NULL
SET @IndexName = NULL

DECLARE @Missing TABLE (
	key_ordinal INT IDENTITY(1,1) NOT NULL,
	column_id INT NOT NULL,
	column_name	SYSNAME	NULL,
	column_usage NVARCHAR(32) NULL
	)

INSERT INTO @Missing
SELECT *
FROM sys.dm_db_missing_index_columns(' + CONVERT(NVARCHAR(32), @Handle) + ')

DECLARE @Count INT = @@ROWCOUNT

SELECT TOP 1
	@Rebuild = 1,
	@Filegroup = [filegroup],
	@IndexName = [index_name]
FROM
	(
	SELECT
		O.name [object_name], I.index_id, I.name [index_name], F.name [filegroup], COUNT(C.column_id) [index_cols], COUNT(M.column_id) [missing_cols]
	FROM ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.index_columns IC
	INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.objects O
	ON O.object_id = IC.object_id
	INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.indexes I
	ON I.index_id = IC.index_id AND
	   I.object_id = IC.object_id
	INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.columns C
	ON C.object_id = IC.object_id AND
	   C.column_id = IC.column_id
	INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.filegroups F
	ON F.data_space_id = I.data_space_id
	LEFT OUTER JOIN @Missing M
	ON M.column_id = IC.column_id AND
	   ( M.column_usage = ''INCLUDE'' OR M.key_ordinal = IC.key_ordinal ) AND
	   IC.is_included_column =
			CASE M.column_usage
				WHEN ''INCLUDE'' THEN 1
				ELSE 0
			END
	WHERE IC.object_id = ' + CONVERT(NVARCHAR(32), @ObjectID) + '
	GROUP BY O.name, I.index_id, I.name, F.name
	) T
WHERE
	T.index_cols = T.missing_cols AND
	T.index_cols = @Count'
	
	EXECUTE sys.sp_executesql
		@TSQL,
		N'@Rebuild BIT OUT, @Filegroup SYSNAME OUT, @IndexName SYSNAME OUT',
		@Rebuild = @Rebuild OUT,
		@Filegroup = @Filegroup OUT,
		@IndexName = @IndexName OUT

	IF @Rebuild = 1
	BEGIN
		INSERT INTO #RINDEXES
		VALUES (
			@Handle,
			(
				SELECT TOP 1
					create_index_part1 + @IndexName + create_index_part2 + 'DROP_EXISTING = ON, ' + create_index_part3 + ' ON ' + QUOTENAME(@Filegroup)
				FROM #CINDEXES
				WHERE index_handle = @Handle
			)
		)		               

		DELETE FROM #CINDEXES
		WHERE index_handle = @Handle
	END

	FETCH NEXT FROM RunPerIndex INTO @Handle, @DatabaseID, @ObjectID, @Impact, @ObjectFullName
END

CLOSE RunPerIndex
DEALLOCATE RunPerIndex

--## SEND EMAIL ##

DECLARE @Recipients VARCHAR(510) = 'dba@net.com;',
		@Subject VARCHAR(255) = UPPER(@@SERVERNAME) + ' Indexes',
		@Body VARCHAR(MAX),
		@RowColor BIT = 0

SET @Body =
'<style type="text/css">body { font-family:"arial"; }</style>
<body>
The following indexes need to be CREATED on ' + UPPER(@@SERVERNAME) + ': <br /><br />
<table border="1" cellspacing="0" cellpadding="10" style="text-align:center">
	<thead>
		<tr style="background-color:#525252; color:White">
			<th>Create Statement</th>
		</tr>
	</thead>
	<tbody>
'

SELECT
	@RowColor =
		CASE @RowColor
			WHEN 0 THEN 1
			ELSE 0
		END,
	@Body +=
		'<tr' + CASE @RowColor WHEN 1 THEN ' style="background-color:#D3D3D3"' ELSE '' END + '>' + CHAR(13) +
		'<td>' + create_index_part1 + create_index_name + create_index_part2 + create_index_part3 + '</td>' + CHAR(13) +
		'</tr>' + CHAR(13)
FROM #CINDEXES
ORDER BY improvement_measure DESC

SET @Body +=
'	<tbody>
</table>
<br />
<br />
The following indexes need to be REBUILT on ' + UPPER(@@SERVERNAME) + ': <br /><br />
<table border="1" cellspacing="0" cellpadding="10" style="text-align:center">
	<thead>
		<tr style="background-color:#525252; color:White">
			<th>Rebuild Statement</th>
		</tr>
	</thead>
	<tbody>
'

SET @RowColor = 0

SELECT
	@RowColor =
		CASE @RowColor
			WHEN 0 THEN 1
			ELSE 0
		END,
	@Body +=
		'<tr' + CASE @RowColor WHEN 1 THEN ' style="background-color:#D3D3D3"' ELSE '' END + '>' + CHAR(13) +
		'<td>' + sql_statement + '</td>' + CHAR(13) +
		'</tr>' + CHAR(13)
FROM #RINDEXES

SET @Body +=
'</table>
</body>'

EXECUTE msdb.dbo.sp_send_dbmail
	@recipients = @Recipients,
	@profile_name = 'WON Notification',
	@body_format = 'html',
	@subject = @Subject,
	@body = @Body;

DROP TABLE #CINDEXES
DROP TABLE #RINDEXES