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

DECLARE @dbName   SYSNAME
  ,@schemaName SYSNAME
  ,@ObjectName SYSNAME
  ,@sql   NVARCHAR(max)
SELECT @dbName='DBname'
  ,@schemaName='dbo'
  ,@ObjectName='tablename'

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

 

 

select o.name ,i.name ,i.type from

sys.indexes i inner join sys.objects o on i.object_id = o.object_id

inner join sys.partitions p on p.index_id = i.index_id and p.object_id = i.object_id

where p.partition_id = 72057594065518592