萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 索引碎片檢測與重建:從監控到治理

碎片危害

長期增刪改導致頁分裂、邏輯順序與物理順序偏離、頁面填充率下降,引發掃描 I/O 增加、緩存命中率降低、統計信息失真,最終拖慢查詢。

碎片檢測:sys.dm_db_index_physical_stats

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;

閾值處理策略(Microsoft 建議)

碎片率動作說明
< 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;

自動化維護建議

  • 使用 Ola Hallengren 維護腳本 或 Maintenance Plan 定期(周/日)檢測並自適應 REORGANIZE/REBUILD。
  • 設置 FILLFACTOR(如 80-90)預留頁面空間,減少頁分裂頻率。
  • 大表重建選 ONLINE = ON(Enterprise 版),避免長時間排他鎖阻塞業務。
  • 重建後 UPDATE STATISTICS 或依賴 AUTO_UPDATE_STATISTICS。
copyright © 萬盛學電腦網 all rights reserved