SQL Server 2005+ 索引碎片自動化整理腳本：重建與重組智能判斷

碎片整理策略：閾值驅動

• avg_fragmentation_in_percent < 10%：無需處理。
• 10% ≤ 碎片 < 30%：`ALTER INDEX ... REORGANIZE` (線上、單執行緒、輕量、不更新統計)。
• 碎片 ≥ 30%：`ALTER INDEX ... REBUILD` (可 `ONLINE=ON` 企業版、多執行緒、更新統計、重設填充因子)。
• 頁面數 < 1000 (小表)：碎片影響微乎其微，可忽略或直接 `REBUILD`。

生產級自動化腳本 (支援所有使用者資料庫)

SET NOCOUNT ON;

-- 參數設定
DECLARE @FragThresholdReorg  FLOAT = 10.0;   -- 重組下限
DECLARE @FragThresholdRebuild FLOAT = 30.0;  -- 重建下限
DECLARE @MinPageCount        INT   = 1000;   -- 忽略小表
DECLARE @FillFactor          INT   = 90;     -- 重建填充因子
DECLARE @OnlineRebuild       BIT   = 1;      -- 企業版啟用 ONLINE
DECLARE @MaxDop              INT   = 4;      -- 重建並行度
DECLARE @LogToTable          BIT   = 1;      -- 記錄操作歷史

-- 建立操作日志表 (可選)
IF @LogToTable = 1 AND OBJECT_ID('dbo.IndexMaintenanceLog') IS NULL
BEGIN
    CREATE TABLE dbo.IndexMaintenanceLog (
        ID INT IDENTITY PRIMARY KEY,
        DbName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME,
        FragPct FLOAT, PageCount INT, ActionTaken VARCHAR(20),
        StartTime DATETIME2, EndTime DATETIME2, DurationMs INT, ErrorMsg NVARCHAR(MAX)
    );
END

-- 游標遍歷所有線上非系統資料庫
DECLARE @DbName SYSNAME;
DECLARE db_cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE' AND database_id > 4; -- 排除 master, tempdb, model, msdb

OPEN db_cur;
FETCH NEXT FROM db_cur INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 動態 SQL 在目標資料庫內容執行
    DECLARE @Sql NVARCHAR(MAX) = N'
    USE [' + @DbName + '];
    DECLARE @Msg NVARCHAR(MAX);
    DECLARE idx_cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT
            s.name AS SchemaName,
            o.name AS TableName,
            i.name AS IndexName,
            ips.avg_fragmentation_in_percent,
            ips.page_count,
            i.index_id,
            i.type_desc
        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
        JOIN sys.objects o ON i.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE ips.avg_fragmentation_in_percent >= ' + CAST(@FragThresholdReorg AS VARCHAR(10)) + '
          AND ips.page_count >= ' + CAST(@MinPageCount AS VARCHAR(10)) + '
          AND i.type_desc IN (''CLUSTERED'', ''NONCLUSTERED'') -- 排除 HEAP (需 REBUILD 轉 CLUSTERED)
          AND o.is_ms_shipped = 0;

    DECLARE @Sch SYSNAME, @Tbl SYSNAME, @Idx SYSNAME, @Frag FLOAT, @Pages INT, @IdxId INT, @Type VARCHAR(20);
    DECLARE @Action VARCHAR(20), @Cmd NVARCHAR(MAX), @St DATETIME2, @Et DATETIME2;

    OPEN idx_cur;
    FETCH NEXT FROM idx_cur INTO @Sch, @Tbl, @Idx, @Frag, @Pages, @IdxId, @Type;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @St = SYSDATETIME();
        SET @Action = CASE WHEN @Frag >= ' + CAST(@FragThresholdRebuild AS VARCHAR(10)) + ' THEN ''REBUILD'' ELSE ''REORGANIZE'' END;
        SET @Cmd = N''ALTER INDEX ['' + @Idx + N''] ON ['' + @Sch + N''].['' + @Tbl + N''] '' + @Action;

        IF @Action = ''REBUILD''
            SET @Cmd += N'' WITH (FILLFACTOR = '' + CAST(' + CAST(@FillFactor AS VARCHAR(10)) + ' AS VARCHAR(3)) + N'', ONLINE = '' + CASE WHEN ' + CAST(@OnlineRebuild AS VARCHAR(5)) + ' = 1 THEN N''ON'' ELSE N''OFF'' END + N'', MAXDOP = '' + CAST(' + CAST(@MaxDop AS VARCHAR(10)) + ' AS VARCHAR(3)) + N'')'';
        ELSE
            SET @Cmd += N'' WITH (LOB_COMPACTION = ON)''; -- REORGANIZE 選項

        BEGIN TRY
            EXEC sp_executesql @Cmd;
            SET @Et = SYSDATETIME();
            SET @Msg = ''SUCCESS'';
        END TRY
        BEGIN CATCH
            SET @Et = SYSDATETIME();
            SET @Msg = ERROR_MESSAGE();
        END CATCH;

        ' + CASE WHEN @LogToTable = 1 THEN N'
        INSERT INTO dbo.IndexMaintenanceLog (DbName, SchemaName, TableName, IndexName, FragPct, PageCount, ActionTaken, StartTime, EndTime, DurationMs, ErrorMsg)
        VALUES (DB_NAME(), @Sch, @Tbl, @Idx, @Frag, @Pages, @Action, @St, @Et, DATEDIFF(MS, @St, @Et), @Msg);
        ' ELSE N'PRINT ''[' + @DbName + '] '' + @Action + '' '' + @Sch + ''.'' + @Tbl + ''.'' + @Idx + '' Frag:'' + CAST(@Frag AS VARCHAR(10)) + '' Pages:'' + CAST(@Pages AS VARCHAR(10)) + '' '' + @Msg;' END + '

        FETCH NEXT FROM idx_cur INTO @Sch, @Tbl, @Idx, @Frag, @Pages, @IdxId, @Type;
    END

    CLOSE idx_cur; DEALLOCATE idx_cur;
    ';

    EXEC sp_executesql @Sql;
    FETCH NEXT FROM db_cur INTO @DbName;
END

CLOSE db_cur; DEALLOCATE db_cur;
PRINT 'Index Maintenance Completed.';

關鍵優化細節解析

1. `sys.dm_db_index_physical_stats` 模式選擇：
   • `LIMITED` (預設)：最快，掃描 1% 頁面 (最少)，適合大資料庫例行維護。
   • `SAMPLED`：掃描 1% 頁面 (最多)，精度較高。
   • `DETAILED`：掃描所有頁面，**極慢、極耗 I/O**，嚴禁生產高峰期執行，僅用於疑難排查。
2. HEAP (堆表) 處理：HEAP 無法 `REORGANIZE`。選項：
   ① `ALTER TABLE ... REBUILD` (2008+，整理 HEAP) ② 建立 Clustered Index (推薦) ③ 忽略。
3. LOB_COMPACTION = ON：`REORGANIZE` 時壓縮 LOB 資料 (預設 OFF)，避免 LOB 碎片殘留。
4. 統計資訊同步更新：`REBUILD` 以 `FULLSCAN` 更新統計；`REORGANIZE` **不更新統計** → 維護結束後建議額外執行 `UPDATE STATISTICS` (或啟用 `AUTO_UPDATE_STATISTICS`)。
5. 分區索引：大表分區索引可指定 `PARTITION = n` 僅整理熱分區 (歷史分區通常唯讀無碎片)。
6. TempDB 碎片：TempDB 重啟即重建，無需維護。

排程部署建議

• 頻率：OLTP 高寫入庫 → 每日/每週；唯讀/歸檔庫 → 每月/每季。
• 時間窗：業務低峰期 (如 02:00-04:00)；配合 `MAXDOP` 限制並行度避免衝擊業務。
• 監控：檢查 `IndexMaintenanceLog` 失敗記錄；監控維護期間 `Buffer Cache Hit Ratio` / `Page Life Expectancy` / `Disk Latency` 指標。
• Ola Hallengren 維護方案：業界標準開源腳本 (ola.hallengren.com)，功能更完備 (備份/完整性檢查/索引/統計)，強烈建議生產環境直接採用而非自寫。

手動單表/單索引快速檢查與整理

-- 查看指定表碎片
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(), OBJECT_ID('dbo.BigTable'), NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE i.name IS NOT NULL;

-- 手動重建 (企業版 ONLINE)
ALTER INDEX [IX_BigTable_KeyCol] ON [dbo].[BigTable] REBUILD WITH (FILLFACTOR = 90, ONLINE = ON, MAXDOP = 4);

-- 手動重組
ALTER INDEX [IX_BigTable_KeyCol] ON [dbo].[BigTable] REORGANIZE WITH (LOB_COMPACTION = ON);