SQL Server 批量生成索引 REBUILD 腳本：按表名、索引大小篩選、DATA_COMPRESSION=PAGE 壓縮選項一鍵輸出

業務場景

大表刪除/更新後索引碎片嚴重，需針對特定表、特定大小以上的索引批量生成 `REBUILD WITH (DATA_COMPRESSION = PAGE)` 腳本。

參數化生成腳本

DECLARE @TableName SYSNAME = 'tblOrders';  -- 目標表
DECLARE @MinIndexSizeMB INT = 0;           -- 最小索引大小 (MB)，0 表示全部

SELECT 
    'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.OBJECT_ID)) + '.' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) + 
    ' REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, MAXDOP = 4);' AS RebuildScript
FROM sys.indexes i
JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
WHERE i.OBJECT_ID = OBJECT_ID(@TableName)
  AND i.index_id > 0                    -- 排除堆
  AND i.type_desc <> 'HEAP'
GROUP BY i.OBJECT_ID, i.index_id, i.name
HAVING (8.0 * SUM(a.used_pages) / 1024) > @MinIndexSizeMB
ORDER BY (8.0 * SUM(a.used_pages) / 1024) DESC;

輸出示例

ALTER INDEX [IX_tblOrders_CustomerID] ON [dbo].[tblOrders] REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, MAXDOP = 4);
ALTER INDEX [PK_tblOrders] ON [dbo].[tblOrders] REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, MAXDOP = 4);

關鍵選項解析

執行策略建議

1. 維護窗口執行 (如凌晨 02:00-04:00)
2. 先 `DBCC SHOWCONTIG` / `sys.dm_db_index_physical_stats` 確認碎片 > 30% 3. 分批執行，單次 ≤ 5-10 個大索引，監控日志增長 4. 完成後 `UPDATE STATISTICS` 全掃描 5. 記錄執行耗時、日志增長、空間釋放量，建立基線

一鍵檢查碎片腳本 (配套使用)

SELECT 
    OBJECT_SCHEMA_NAME(ips.OBJECT_ID) + '.' + OBJECT_NAME(ips.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count * 8.0 / 1024 AS SizeMB,
    CASE WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
         WHEN ips.avg_fragmentation_in_percent > 5 THEN 'REORGANIZE'
         ELSE 'OK' END AS Action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.OBJECT_ID = ips.OBJECT_ID AND i.index_id = ips.index_id
WHERE ips.index_id > 0 AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;