SQL NOTE--DMV For Index

--查看用户查询使用索引情况
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