SQL Server 2008 R2 索引重建與重組自動化腳本

索引碎片檢查

使用 sys.dm_db_index_physical_stats 檢視碎片程度：

SELECT
    OBJECT_NAME(object_id) AS objectname,
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

自動化重建/重組腳本 (來源：線上叢書)

SET NOCOUNT ON;
DECLARE @objectid INT, @indexid INT, @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130), @objectname NVARCHAR(130), @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT, @partitions BIGINT, @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- 將需處理的分割區存入暫存表
SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;

WHILE (1=1)
BEGIN
    FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
    IF @@FETCH_STATUS < 0 BREAK;

    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid;

    SELECT @partitioncount = COUNT(*) FROM sys.partitions
    WHERE object_id = @objectid AND index_id = @indexid;

    -- 碎片 30% 為閾值：<30% 重組，>=30% 重建
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);
    PRINT N'Executed: ' + @command;
END;

CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
GO

關鍵點說明

• REORGANIZE：線上操作、鎖定較輕、適合輕度碎片 (10%-30%)。
• REBUILD：重建索引、可選 ONLINE=ON (Enterprise 版)、更新統計資料、適合重度碎片 (>30%)。
• 腳本自動判斷分割區數量，若>1 則加上 PARTITION 子句。
• 建議納入維護計畫定期執行 (如每週)，並搭配 UPDATE STATISTICS。

此腳本為官方推薦範例，可直接部署於生產環境作為索引維護基礎方案。