SqlServer查找缺失索引和查找无效索引

SqlServer查找缺失索引

系统慢，卡，瓶颈在数据库，数据库服务器cpu飙升，但没发现死锁。

发现了一些慢查询，首先想到的加索引。

那么问题来了，怎么加，加哪些？

好在SQLSERVER提供了一些“自动”查找功能，比如 DMV，

附上sql代码：

SELECT
	avg_total_user_cost,
	avg_user_impact,
	migs.group_handle,
	mid.*
FROM
	sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig 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
)

SELECT
	avg_total_user_cost,
	avg_user_impact,
	avg_total_user_cost * avg_user_impact * (user_scans + user_seeks) AS PossibleImprovement,
	[statement] AS TableName,
	equality_columns,
	'CREATE INDEX [IDX_' + CONVERT (
		VARCHAR (32),
		GS.group_handle
	) + '_' + CONVERT (VARCHAR(32), D.index_handle) + '_' + REPLACE(
		REPLACE(
			REPLACE([STATEMENT], ']', ''),
			'[',
			''
		),
		'.',
		''
	) + ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '') + CASE
WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN
	','
ELSE
	''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(
	' INCLUDE (' + included_columns + ')',
	''
) AS Create_Index_Syntax,
 last_user_seek,
 last_user_scan
FROM
	sys.dm_db_missing_index_groups AS G
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
ORDER BY
	avg_user_impact DESC 
-- PossibleImprovement desc

 

可能一些无效索引

SELECT
	ind.index_id,
	obj.name AS TableName,
	ind.name AS IndexName,
	ind.type_desc,
	indUsage.user_seeks,
	indUsage.user_scans,
	indUsage.user_lookups,
	indUsage.user_updates,
	indUsage.last_system_seek,
	indUsage.last_user_scan,
	'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand
FROM
	sys.indexes AS ind
INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id
LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id
AND ind.index_id = indUsage.index_id
WHERE
	ind.type_desc <> 'HEAP'
AND obj.type <> 'S'
AND OBJECTPROPERTY(
	obj.object_id,
	'isusertable'
) = 1
AND (
	ISNULL(indUsage.user_seeks, 0) = 0
	AND ISNULL(indUsage.user_scans, 0) = 0
	AND ISNULL(indUsage.user_lookups, 0) = 0
)
ORDER BY
	obj.name,
	ind.name