萬盛學電腦網

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

SQL Server 索引維護實戰:碎片檢測、重組重建策略與自動化腳本

索引維護核心邏輯:碎片率決定動作

avg_fragmentation_in_percent 動作 T-SQL 指令 線上支援
< 5% 無需處理 — —
5% - 30% 重組 ALTER INDEX ... REORGANIZE ✅ 完全線上
> 30% 重建 ALTER INDEX ... REBUILD WITH (ONLINE=ON) ✅ Enterprise/Developer 版

Step 1:掃描密度 / 碎片率檢測

-- 方法 1:sys.dm_db_index_physical_stats (推薦,可指定掃描模式)
DECLARE @db_id INT = DB_ID();
SELECT 
  OBJECT_NAME(ips.object_id) AS TableName,
  i.name AS IndexName,
  ips.index_type_desc,
  ips.index_level,
  ips.avg_fragmentation_in_percent,
  ips.avg_page_space_used_in_percent AS Page_Density,
  ips.page_count,
  ips.record_count
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- 忽略小表
  AND ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 方法 2:DBCC SHOWCONTIG (舊版相容,輸出較詳細)
-- DBCC SHOWCONTIG ('TableName') WITH FAST, TABLERESULTS, ALL_INDEXES;

關鍵指標解讀

  • avg_fragmentation_in_percent:邏輯碎片 (頁面順序不連續) → 影響範圍掃描預讀效率
  • avg_page_space_used_in_percent:頁面密度 (填充度) → 過低浪費記憶體/磁碟,過高易分裂
  • fragment_count / avg_fragment_size_in_pages:DETAILED 模式下的深度碎片資訊

Step 2:針對性重建 - 從單索引到全表

單一索引重建 (指定 PK/索引名)

-- 重建特定索引,保留原 Fill Factor
ALTER INDEX PK_Orders ON Orders REBUILD WITH (ONLINE = ON, MAXDOP = 4);

-- 重建並調整 Fill Factor (針對隨機鍵)
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);

全表所有索引重建

-- 重建表上所有索引 (含 PK、XML、空間索引)
ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON, MAXDOP = 4, FILLFACTOR = 80);

-- 僅重建非叢集索引 (保留叢集索引順序)
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'ALTER INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' REBUILD WITH (ONLINE=ON, MAXDOP=4);' + CHAR(10)
FROM sys.indexes
WHERE object_id = OBJECT_ID('Orders') AND index_id > 1 AND type_desc <> 'HEAP';
EXEC sp_executesql @sql;

Step 3:統計資料同步更新 (比重建更關鍵)

-- 重建索引會自動以 FULLSCAN 更新該索引統計資料
-- 但非索引列統計、手動建立的統計需單獨更新

-- 更新表上所有統計 (採樣)
UPDATE STATISTICS Orders WITH RESAMPLE;

-- 關鍵表全掃描更新 (維護窗口執行)
UPDATE STATISTICS Orders WITH FULLSCAN, ALL, NORECOMPUTE;

-- 啟用自動更新統計 (資料庫級)
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON; -- 非同步更新,避免查詢等待

生產級自動化維護腳本 (Ola Hallengren 風格簡化版)

CREATE PROCEDURE dbo.IndexMaintenance
    @FragmentationLow  INT = 5,
    @FragmentationHigh INT = 30,
    @FillFactor        INT = 80,
    @MaxDOP            INT = 4,
    @Online            BIT  = 1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = '';
    DECLARE @msg NVARCHAR(4000);
    
    -- 建立暫存表收集需處理索引
    CREATE TABLE #IdxMaint (
        SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME,
        IndexID INT, FragPct FLOAT, PageCnt BIGINT, Action VARCHAR(20)
    );
    
    INSERT INTO #IdxMaint
    SELECT 
        OBJECT_SCHEMA_NAME(ips.object_id),
        OBJECT_NAME(ips.object_id),
        i.name,
        ips.index_id,
        ips.avg_fragmentation_in_percent,
        ips.page_count,
        CASE 
            WHEN ips.avg_fragmentation_in_percent >= @FragmentationHigh THEN 'REBUILD'
            WHEN ips.avg_fragmentation_in_percent >= @FragmentationLow THEN 'REORGANIZE'
            ELSE 'NONE'
        END
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.page_count > 1000
      AND ips.avg_fragmentation_in_percent >= @FragmentationLow
      AND i.is_disabled = 0 AND i.is_hypothetical = 0;
    
    -- 執行維護
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT SchemaName, TableName, IndexName, Action FROM #IdxMaint WHERE Action <> 'NONE';
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @Action;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @msg = '[' + CONVERT(VARCHAR, GETDATE(), 120) + '] ' + @Action + ' ' + @SchemaName + '.' + @TableName + '.' + @IndexName;
        RAISERROR(@msg, 0, 1) WITH NOWAIT; -- 即時輸出到 Messages
        
        IF @Action = 'REBUILD'
            SET @sql = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) 
                     + ' REBUILD WITH (ONLINE = ' + CASE @Online WHEN 1 THEN 'ON' ELSE 'OFF' END + ', MAXDOP = ' + CAST(@MaxDOP AS VARCHAR) + ', FILLFACTOR = ' + CAST(@FillFactor AS VARCHAR) + ');';
        ELSE
            SET @sql = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REORGANIZE;';
        
        BEGIN TRY
            EXEC sp_executesql @sql;
            SET @msg = '[' + CONVERT(VARCHAR, GETDATE(), 120) + '] SUCCESS';
        END TRY
        BEGIN CATCH
            SET @msg = '[' + CONVERT(VARCHAR, GETDATE(), 120) + '] FAILED: ' + ERROR_MESSAGE();
        END CATCH
        RAISERROR(@msg, 0, 1) WITH NOWAIT;
        
        FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @Action;
    END
    CLOSE cur; DEALLOCATE cur;
    DROP TABLE #IdxMaint;
END;

Agent Job 排程部署

-- 每週日 02:00 執行 (維護窗口)
EXEC msdb.dbo.sp_add_job @job_name = 'Weekly_Index_Maintenance';
EXEC msdb.dbo.sp_add_jobstep @job_name = 'Weekly_Index_Maintenance', 
    @step_name = 'Run Index Maintenance', 
    @subsystem = 'TSQL', 
    @command = 'EXEC dbo.IndexMaintenance @FragmentationLow=5, @FragmentationHigh=30, @FillFactor=80, @MaxDOP=4, @Online=1;',
    @database_name = 'YourDB';
EXEC msdb.dbo.sp_add_jobschedule @job_name = 'Weekly_Index_Maintenance',
    @freq_type = 8, @freq_interval = 1, @active_start_time = 020000; -- Weekly Sunday 02:00
EXEC msdb.dbo.sp_add_jobserver @job_name = 'Weekly_Index_Maintenance';

進階:統計資料獨立維護作業

CREATE PROCEDURE dbo.StatisticsMaintenance
AS
BEGIN
    SET NOCOUNT ON;
    -- 1. 更新全資料庫統計 (採樣) - 每日低峰
    EXEC sp_updatestats; -- sp_updatestats 僅更新需更新的統計 (rowmodctr 閾值)
    
    -- 2. 關鍵大表全掃描 - 每週
    UPDATE STATISTICS dbo.Orders WITH FULLSCAN, ALL;
    UPDATE STATISTICS dbo.Customers WITH FULLSCAN, ALL;
    -- ... 其他核心表
END;

監控與告警整合

  • 作業失敗告警: Agent Job 通知 → Email / Teams / 企業微信
  • 碎片率趨勢: 將 dm_db_index_physical_stats 歷史寫入基線表,繪製趨勢圖
  • 維護窗口超時: 設定 Job Step 逾時 (如 4 小時),超時自動取消並告警
  • TempDB 壓力: 重建大索引時 TempDB 可能暴增,預先擴大 TempDB、監控版本存儲

版本與版本差異提醒

版本/版本 ONLINE REBUILD RESUMABLE REBUILD 備註
Standard Edition ❌ (離線鎖表) ❌ 僅 REORGANIZE 線上
Enterprise/Developer ✅ ✅ (SQL 2017+) RESUMABLE = ON, MAX_DURATION = 240 可暫停續傳
Azure SQL / Managed Instance ✅ ✅ 建議啟用自動調優

索引維護不是「一勞永逸」的設定,而是持續監控 → 量化決策 → 自動化執行 → 效果驗證的閉環流程。將上述腳本納入 CI/CD 部署標準,配合 dbatools PowerShell 模組跨實例批量管理,才是現代 DBA 的專業姿態。

copyright © 萬盛學電腦網 all rights reserved