INDEX--查看索引使用情况

--==============================================
--查看可能缺失的索引
SELECT 
mig.*
,migs.*
,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)



--=======================================================
--查看索引的使用情况,重点关注索引为0和1的user_scans
--再关注(user_scans+user_seeks+user_lookups)/user_updates的比例,比例
SELECT OBJECT_NAME(S.object_id,S.database_id) AS TableName
,ISNULL(I.name,‘No Index‘) AS IndexName
,(S.[user_seeks]+S.[user_scans]+S.[user_lookups])/(S.[user_updates]+1) AS UseRate 
,S.* 
FROM sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS I
ON I.object_id=S.object_id
AND S.index_id=I.index_id


--=======================================================
--索引在重建或删除新建时sys.dm_db_index_usage_stats中相关的数据会被清除
--索引在重整是不会清除sys.dm_db_index_usage_stats的数据

--查看索引碎片
--‘DETAILED‘选项会导致扫描全表，慎用
SELECT OBJECT_NAME (ips.[object_id]) AS ‘Object Name‘,
si.name AS ‘Index Name‘,   
ROUND (ips.avg_fragmentation_in_percent, 2) AS ‘Fragmentation‘,   
ips.page_count AS ‘Pages‘,   
ROUND (ips.avg_page_space_used_in_percent, 2) AS ‘Page Density‘
FROM sys.dm_db_index_physical_stats (DB_ID (‘SQLskillsDB‘), NULL, NULL, NULL, ‘DETAILED‘)
ips  CROSS APPLY sys.indexes si 
WHERE   si.object_id = ips.object_id   
AND si.index_id = ips.index_id   
AND ips.index_level = 0 -- only the leaf level   
AND ips.avg_fragmentation_in_percent > 10; -- filter on fragmentation 
GO