您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 索引維護神器：sp_RefreshIndex 存儲過程詳解與自動化部署

編輯：關於SqlServer

背景：索引碎片導致的性能危機

上週客戶反饋系統在處理大量數據時頻繁出現網絡超時。經排查發現，罪魁禍首是索引碎片率過高，導致查詢計劃退化、I/O 激增。解決方案很明確：定期重新組織或重新生成索引。為了通用、可控、可調度，我編寫了通用存儲過程 sp_RefreshIndex。

核心邏輯：雙閾值策略

@Reorganize_Fragmentation_Percent (默認 5%)：邏輯碎片率 > 5% 執行 ALTER INDEX ... REORGANIZE（在線、輕量、不鎖表）。
@Rebuild_Fragmentation_Percent (默認 30%)：邏輯碎片率 > 30% 執行 ALTER INDEX ... REBUILD（重建統計信息、壓縮頁面、可選 ONLINE=ON）。
碎片率 ≤ 5% 直接跳過，避免無謂開銷。

完整存儲過程代碼

USE master;
GO
IF OBJECT_ID('sp_RefreshIndex') IS NOT NULL
    DROP PROC sp_RefreshIndex;
GO

CREATE PROC sp_RefreshIndex
    @Reorganize_Fragmentation_Percent SMALLINT = 5,   -- 重組閾值
    @Rebuild_Fragmentation_Percent    SMALLINT = 30   -- 重建閾值
AS
BEGIN
    /* 調用示例：
       - 當前實例所有用戶庫：EXEC sys.sp_MSforeachdb 'USE ?; EXEC sp_RefreshIndex'
       - 單庫：USE YourDB; EXEC sp_RefreshIndex
    */

    -- 1. 跳過系統庫
    IF DB_NAME() IN ('master','model','msdb','tempdb') RETURN;

    -- 2. 若無索引碎片超過重組閾值，直接返回
    IF NOT EXISTS (
        SELECT 1 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
        WHERE index_id > 0 AND avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent
    ) RETURN;

    -- 3. 打印標題
    PRINT REPLICATE('-',60) + CHAR(13)+CHAR(10) +
          REPLICATE(' ',14) + N'對數據庫 ' + QUOTENAME(DB_NAME()) + N' 進行索引優化' +
          REPLICATE(' ',20) + CHAR(13)+CHAR(10);

    DECLARE @sql NVARCHAR(2000), @msg NVARCHAR(2000);

    -- 4. 游標遍歷需維護的索引
    DECLARE cur_x CURSOR LOCAL FAST_FORWARD FOR
        SELECT
            'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' +
            QUOTENAME(SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) +
            CASE WHEN s.avg_fragmentation_in_percent <= @Rebuild_Fragmentation_Percent
                 THEN ' REORGANIZE;' ELSE ' REBUILD;' END AS [sql],
            CASE WHEN s.avg_fragmentation_in_percent <= @Rebuild_Fragmentation_Percent
                 THEN N'重新組織索引：' ELSE N'重新生成索引：' END +
            QUOTENAME(SCHEMA_NAME(i.object_id)) + '.' +
            QUOTENAME(OBJECT_NAME(i.object_id)) + '.' + QUOTENAME(i.name) AS [msg]
        FROM sys.indexes i
        INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
            ON s.object_id = i.object_id AND s.index_id = i.index_id
        WHERE i.index_id > 0
          AND s.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent
        ORDER BY OBJECT_NAME(i.object_id), i.index_id;

    OPEN cur_x;
    FETCH NEXT FROM cur_x INTO @sql, @msg;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(@sql);          -- 執行維護命令
        PRINT @msg;          -- 記錄日誌
        FETCH NEXT FROM cur_x INTO @sql, @msg;
    END

    CLOSE cur_x;
    DEALLOCATE cur_x;
END;
GO

-- 標記為系統對象，便於 sp_MSforeachdb 直接調用
EXEC sp_ms_marksystemobject 'sp_RefreshIndex';
GO

部署與自動化

1. 單庫手動執行

USE YourBusinessDB;
EXEC sp_RefreshIndex;  -- 使用默認閾值 5% / 30%
-- 或自定義閾值
EXEC sp_RefreshIndex @Reorganize_Fragmentation_Percent = 10, @Rebuild_Fragmentation_Percent = 40;

2. 實例級批量執行（推薦）

EXEC sys.sp_MSforeachdb 'USE ?; EXEC sp_RefreshIndex';

3. SQL Server Agent 定時作業

新建作業 → 步驟：EXEC sys.sp_MSforeachdb 'USE ?; EXEC sp_RefreshIndex';
排程：建議每月或雙周低峰期執行（如凌晨 02:00）。
啟用「記錄到表」或「郵件通知」便於審計。

適用版本與注意事項

✅ 已驗證：SQL Server 2005 SP4 / 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017 / 2019 / 2022。
⚠️ REBUILD 預設為離線操作（鎖表），企業版可加 WITH (ONLINE = ON) 實現線上重建；標準版請安排維護窗口。
⚠️ 大表重建會產生大量事務日誌，確保日誌磁盤空間充足，或切換到 BULK_LOGGED 恢復模式。
💡 可擴展：在游標循環中加入 WAITFOR DELAY '00:00:01' 緩解 I/O 壓力；或記錄執行耗時到日誌表供事後分析。