長期增刪改導致頁分裂、邏輯順序與物理順序偏離、頁面填充率下降,引發掃描 I/O 增加、緩存命中率降低、統計信息失真,最終拖慢查詢。
DECLARE @DB_ID INT = DB_ID(N'AdventureWorks2008R2');
DECLARE @OBJ_ID INT = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');
IF @DB_ID IS NULL PRINT 'Invalid DB';
ELSE IF @OBJ_ID IS NULL PRINT 'Invalid Object';
ELSE
BEGIN
SELECT
OBJECT_NAME(IPS.OBJECT_ID) AS TableName,
I.NAME AS IndexName,
IPS.INDEX_TYPE_DESC,
IPS.AVG_FRAGMENTATION_IN_PERCENT,
IPS.PAGE_COUNT,
IPS.RECORD_COUNT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(@DB_ID, @OBJ_ID, NULL, NULL, 'LIMITED') IPS
JOIN SYS.INDEXES I ON I.OBJECT_ID = IPS.OBJECT_ID AND I.INDEX_ID = IPS.INDEX_ID
WHERE IPS.AVG_FRAGMENTATION_IN_PERCENT > 10 -- 關注閾值
ORDER BY IPS.AVG_FRAGMENTATION_IN_PERCENT DESC;
END;
| 碎片率 | 動作 | 說明 |
|---|---|---|
| < 5% | 無需處理 | 健康狀態 |
| 5% - 30% | ALTER INDEX ... REORGANIZE | 在線、單線程、重排葉頁、不更新統計 |
| > 30% | ALTER INDEX ... REBUILD | 重建 B 樹、可選 ONLINE=ON、更新統計、需更多鎖/日志 |
-- 重建整表(含所有索引)
ALTER TABLE Schema.Table REBUILD [WITH (ONLINE = ON, FILLFACTOR = 80)];
-- 重建單個索引
ALTER INDEX IX_Name ON Schema.Table REBUILD [WITH (ONLINE = ON)];
-- 重組單個索引
ALTER INDEX IX_Name ON Schema.Table REORGANIZE;
-- 重組整表所有索引
ALTER INDEX ALL ON Schema.Table REORGANIZE;
FILLFACTOR(如 80-90)預留頁面空間,減少頁分裂頻率。ONLINE = ON(Enterprise 版),避免長時間排他鎖阻塞業務。UPDATE STATISTICS 或依賴 AUTO_UPDATE_STATISTICS。