摘要:[SQL]查看Index的Fragmentation
--查看某DB的Index fragmentation百分比
SELECT i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(DB_ID('YOUR_DB_NAME'),
OBJECT_ID('currency'), NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
order by [Fragmentation %] DESC
一般來說，如果大於30%的話，建議Rebuild Index，如果10%~30%之間，建議reorganization。
由下圖來看，我們的一堆Index需要Rebuild了!
單個表
DBCC SHOWCONTIG(tb)
人生到處知何似
應似飛鴻踏雪泥
