获取当某个表所有索引使用情况

用于分析是否有不被使用的索引

DECLARE @dbName			SYSNAME
		,@schemaName	SYSNAME
		,@ObjectName	SYSNAME
		,@sql			NVARCHAR(max)
SELECT @dbName='DBName'
		,@schemaName='schemaName'
		,@ObjectName='tbName'


SET @sql='SELECT COUNT(1) AS DataCount FROM '+@dbName+'.'+@schemaName+'.'+@ObjectName+' WITH (NOLOCK)'

EXEC sp_executesql @sql


;with data
as (

SELECT 
		DB_NAME(A.database_id)
			+'.'+SCHEMA_NAME(C.schema_id)
			+'.'+OBJECT_NAME(A.object_id) AS TableName
		,B.Name AS IndexName
		,D.Name AS ColumnName
		,A.user_seeks AS IndexSeek
		,A.user_scans AS IndexScan
		,A.user_lookups AS IndexKeyLook
		,A.user_updates AS IndexUpdate
		,CAST(CAST(A.user_seeks*100.00/CASE WHEN (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates)=0 THEN 1 else (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) end 
				AS decimal(6,2)) 
			AS VARCHAR(6))+'%' AS IndexSeekPersent
		,CAST(CAST(A.user_scans*100.00/CASE when (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) =0 then 1 else (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) end
				AS decimal(6,2)) 
			AS VARCHAR(6))+'%' AS IndexScanPersent
		,CAST(CAST(A.user_lookups*100.00/ case when (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) =0 then 1 else (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) end
				AS decimal(6,2)) 
			AS VARCHAR(6))+'%' AS IndexKeyLookPersent
		,CAST(CAST(A.user_updates*100.00/case when (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) = 0 then 1 else (A.user_seeks+A.user_scans+A.user_lookups+A.user_updates) end
				AS decimal(6,2)) 
			AS VARCHAR(6))+'%' AS IndexUpdatePersent
FROM sys.dm_db_index_usage_stats AS A WITH (NOLOCK)
	INNER JOIN sys.indexes AS B WITH (nolock)
	ON A.index_id=B.index_id
	INNER JOIN sys.tables AS C WITH (NOLOCK)
	ON B.object_id=C.object_id 
		AND A.object_id=C.object_id
	INNER JOIN sys.columns AS D WITH (NOLOCK)
	ON A.object_id=D.object_id
	INNER JOIN sys.index_columns AS E WITH (NOLOCK)
	ON B.index_id=E.index_id 
		AND D.column_id=E.column_id 
		AND A.object_id=E.object_id
WHERE DB_NAME(A.database_id)= @dbName
		AND OBJECT_NAME(A.object_id) = @ObjectName
		AND OBJECT_NAME(A.object_id) not like '%Subscription%' --排除Replication相关的系统表
		AND B.is_primary_key = 0   --排除PK
		AND SCHEMA_NAME(C.schema_id)=@schemaName
		AND B.name IS NOT NULL
		AND B.is_disabled=0
		AND B.is_hypothetical=0
)


select *
from data
where (cast(replace(IndexSeekPersent,'%','') as decimal(6,2)) <= 0.1
		or cast(replace(IndexUpdatePersent,'%','') as decimal(6,2)) >=0.9)
		and 
		IndexSeek + IndexScan + IndexKeyLook + IndexUpdate <>0
ORDER BY cast(replace(IndexUpdatePersent,'%','') as decimal(6,2)) DESC
--IndexSeek		DESC
--		,IndexScan		DESC
--		,IndexKeyLook	DESC
--		,IndexUpdate	DESC