--查看用户查询使用索引情况
SELECT
DB_NAME() AS DatabaseName
,SCHEMA_NAME(o.Schema_ID) AS SchemaName
,OBJECT_NAME(s.[object_id]) AS TableName
,i.name AS IndexName
,s.user_updates
,s.user_scans
,s.user_seeks
,s.user_lookups
,s.system_seeks + s.system_scans + s.system_lookups AS SystemUsage
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o
ON i.object_id = O.object_id
ORDER BY S.user_updates DESC
--查看索引碎片情况
SELECT
DB_NAME() AS DatbaseName
,SCHEMA_NAME(o.Schema_ID) AS SchemaName
,OBJECT_NAME(s.[object_id]) AS TableName
,i.name AS IndexName
,s.avg_fragmentation_in_percent
,s.index_depth
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY s.avg_page_space_used_in_percent DESC
--查看索引统计的相关信息
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
ss.name AS SchemaName
,st.name AS TableName
,s.name AS IndexName
,STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
,s.rowcnt AS 'Row Count'
,s.rowmodctr AS 'Number Of Changes'
,CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS '% Rows Changed'
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
ORDER BY SchemaName, TableName, IndexName
