MSSQL 自動偵測並重建索引碎片：存儲過程與維護計畫完整實作

索引碎片成因與影響

• 內部碎片：資料頁填充度不足，導致讀取更多頁面。
• 外部碎片：邏輯順序與物理順序不符，破壞預讀效能。
• 高碎片會導致掃描緩慢、優化器放棄索引改用表掃描，嚴重拖慢查詢。

自動重建存儲過程：USP_IMS_DEFRAGMENT_INDEXES

CREATE PROCEDURE USP_IMS_DEFRAGMENT_INDEXES
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @TABLENAME VARCHAR(128), @INDEXNAME CHAR(255), @EXECSTR VARCHAR(255)
    DECLARE @DBNAME SYSNAME = DB_NAME()

    -- 排除系統資料庫
    IF @DBNAME IN ('master','msdb','model','tempdb')
    BEGIN PRINT '不可在系統資料庫執行'; RETURN END

    -- 階段 1：掃描碎片，記錄至暫存表
    CREATE TABLE #FRAGLIST (TABLENAME CHAR(255), INDEXNAME CHAR(255))

    DECLARE TABLES CURSOR FOR
    SELECT CONVERT(VARCHAR, SO.ID)
    FROM SYSOBJECTS SO JOIN SYSINDEXES SI ON SO.ID = SI.ID
    WHERE SO.TYPE = 'U' AND SI.INDID < 2 AND SI.ROWS > 0

    DECLARE @TABLEIDCHAR VARCHAR(255)
    OPEN TABLES
    FETCH NEXT FROM TABLES INTO @TABLEIDCHAR
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #FRAGLIST
        EXEC ('SELECT OBJECT_NAME(DT.OBJECT_ID), SI.NAME
              FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID('''+@TABLEIDCHAR+'''), NULL, NULL, ''DETAILED'') DT
              INNER JOIN SYS.INDEXES SI ON SI.OBJECT_ID=DT.OBJECT_ID AND SI.INDEX_ID=DT.INDEX_ID
              WHERE DT.INDEX_ID<>0 AND DT.AVG_FRAGMENTATION_IN_PERCENT>10 AND DT.AVG_PAGE_SPACE_USED_IN_PERCENT<75
              ORDER BY DT.AVG_FRAGMENTATION_IN_PERCENT DESC')
        FETCH NEXT FROM TABLES INTO @TABLEIDCHAR
    END
    CLOSE TABLES; DEALLOCATE TABLES

    SELECT * FROM #FRAGLIST  -- 可選：記錄碎片報表

    -- 階段 2：重建碎片索引
    DECLARE INDEXES CURSOR FOR SELECT TABLENAME, INDEXNAME FROM #FRAGLIST
    SELECT 'STARTED DEFRAGMENTING INDEXES AT ' + CONVERT(VARCHAR, GETDATE())
    OPEN INDEXES
    FETCH NEXT FROM INDEXES INTO @TABLENAME, @INDEXNAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @EXECSTR = 'ALTER INDEX ' + @INDEXNAME + ' ON ' + @TABLENAME + ' REBUILD WITH (FILLFACTOR=90, ONLINE=ON)'
        PRINT @EXECSTR
        EXEC(@EXECSTR)
        FETCH NEXT FROM INDEXES INTO @TABLENAME, @INDEXNAME
    END
    CLOSE INDEXES; DEALLOCATE INDEXES

    SELECT 'FINISHED DEFRAGMENTING INDEXES AT ' + CONVERT(VARCHAR, GETDATE())
    DROP TABLE #FRAGLIST
END

排程自動執行：維護計畫精靈

1. SSMS → 管理 → 維護計畫 → 右鍵「維護計畫精靈」。
2. 命名、選擇目標伺服器、驗證方式。
3. 選擇「執行 T-SQL 陳述式任務」，輸入：
   USE [YourDB]; EXEC dbo.USP_IMS_DEFRAGMENT_INDEXES
4. 設定排程（如每週日凌晨 02:00）。
5. 完成精靈，SQL Agent 作業自動建立。

參數說明與調整

• AVG_FRAGMENTATION_IN_PERCENT > 10：碎片超過 10% 才重建，避免過度維護。
• AVG_PAGE_SPACE_USED_IN_PERCENT < 75：頁面填充度低於 75% 才處理。
• FILLFACTOR=90：重建時保留 10% 空間給未來插入，減少分頁分裂。
• ONLINE=ON：Enterprise 版支援線上重建，不鎖定表（Standard 版請移除）。