使用 DMV 查询 Index 碎片化

Posted on 2025-01-01 Edited on 2026-04-29 In MSSQL Word count in article: 359 Reading time ≈ 1 mins.

查询目前 Index 碎片化情形

使用 sys.dm_db_index_physical_stats 查詢資料表碎片化情形

SELECT ix.name [Index Name]
	,s.name [Schema Name]
	,t.name [Table Name]
	,pc.partition_count [Partition Count]
	,ps.avg_page_space_used_in_percent [Page Space Used In Percent]
	,ps.avg_fragmentation_in_percent [Fragmentation In Percent]
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (
	SELECT object_id
		,index_id
		,avg_fragmentation_in_percent
		,avg_page_space_used_in_percent
		,partition_number
	FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
	) ps ON t.object_id = ps.object_id
	AND ix.index_id = ps.index_id
INNER JOIN (
	SELECT object_id
		,index_id
		,COUNT(DISTINCT partition_number) AS partition_count
	FROM sys.partitions
	GROUP BY object_id
		,index_id
	) pc ON t.object_id = pc.object_id
	AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10
	AND ix.name IS NOT NULL
	AND t.name = '[資料表名稱]';

索引分佈分析

Internal fragmentation:

使用 avg_page_space_used_in_percent 分析索引頁面內部的空間使用情況

External fragmentation:

使用 avg_fragmentation_in_percent 分析索引頁在實體儲存中的順序是否連續

索引重組的時機

整理索引內部的實體結構，但不刪除索引

檢查 avg_page_space_used_in_percent 的值建議介於 60 到 75 之間

檢查 avg_fragmentation_in_percent 的值建議介於 10 到 15 之間

索引重組命令:

1	ALTER INDEX [Index Name] ON [Schema Name].[Table Name] REORGANIZE WITH (ONLINE = ON, MAXDOP = 4);

索引重建的時機

刪除索引重新建立

檢查 avg_page_space_used_in_percent 的值建議小於 60

檢查 avg_fragmentation_in_percent 的值建議大於 15

索引重建命令:

1	ALTER INDEX [Index Name] ON [Schema Name].[Table Name] REBUILD WITH (ONLINE = ON, MAXDOP = 4);

📜 參考資料