查询SQL SERVER碎片

-- 查碎片可以用下面的语句(整理碎片可以用 ALTER INDEX index_name ON table_name REBUILD)
SELECT 
    schema_name = SCH.name,
    table_name = TB.name,
    index_name = IX.name,
    IDXF.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXF,
    sys.tables TB,
    sys.schemas SCH,
    SYS.indexes IX
WHERE IDXF.object_id = TB.object_id
    AND TB.schema_id = SCH.schema_id
    AND IDXF.object_id = IX.object_id
    AND IDXF.index_id = IX.index_id
    AND IDXF.avg_fragmentation_in_percent  > 5
ORDER BY table_name, index_name

IDXF.avg_fragmentation_in_percent  > 5的意思是查询碎片大于5%的。