SQL Server 2005 索引重建自動化存儲過程 sp_rebuild_index

高流量互動式網站運行數月後，索引碎片嚴重導致查詢變慢。手工逐個重建索引耗時費力，本文提供一個自動化存儲過程 sp_rebuild_index，可按碎片率閾值批次重建指定資料庫的所有索引。

核心邏輯

• 排除系統資料庫（master、model、msdb、tempdb）
• 利用 sys.dm_db_index_physical_stats 檢測邏輯碎片率（avg_fragmentation_in_percent）
• 僅對碎片率超過閾值（預設 5%）且 index_id > 0（排除堆表）的索引執行 ALTER INDEX ... REBUILD
• 使用游標逐條執行並列印進度

完整建立腳本

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_rebuild_index]
  @Rebuild_Fragmentation_Percent SMALLINT = 5  -- 碎片率閾值，預設 5%
AS
BEGIN
  -- 排除系統資料庫
  IF (DB_NAME() IN ('master','model','msdb','tempdb')) RETURN;

  -- 若無索引超過閾值則直接返回
  IF NOT EXISTS (
    SELECT 1 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) a
    WHERE a.index_id > 0 AND a.avg_fragmentation_in_percent > @Rebuild_Fragmentation_Percent
  ) RETURN;

  PRINT REPLICATE('-',60) + CHAR(13)+CHAR(10) +
        REPLICATE(' ',14) + N'對資料庫 ' + QUOTENAME(DB_NAME()) + N' 進行索引優化' +
        REPLICATE(' ',20) + CHAR(13)+CHAR(10);

  DECLARE @sql NVARCHAR(2000), @str NVARCHAR(2000);
  DECLARE cur_x CURSOR FOR
    SELECT 
      'ALTER INDEX ' + QUOTENAME(a.name) + ' ON ' + 
      QUOTENAME(OBJECT_SCHEMA_NAME(a.object_id)) + '.' + QUOTENAME(OBJECT_NAME(a.object_id)) + ' REBUILD;',
      N'重新生成索引：' + QUOTENAME(OBJECT_SCHEMA_NAME(a.object_id)) + '.' + 
      QUOTENAME(OBJECT_NAME(a.object_id)) + '.' + QUOTENAME(a.name)
    FROM sys.indexes a
    INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) b
      ON b.object_id = a.object_id AND b.index_id = a.index_id
    WHERE a.index_id > 0 
      AND b.avg_fragmentation_in_percent > @Rebuild_Fragmentation_Percent
    ORDER BY OBJECT_NAME(a.object_id), a.index_id;

  OPEN cur_x;
  FETCH NEXT FROM cur_x INTO @sql, @str;
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    PRINT @sql;
    EXEC(@sql);
    PRINT @str;
    FETCH NEXT FROM cur_x INTO @sql, @str;
  END
  CLOSE cur_x;
  DEALLOCATE cur_x;
END
GO

調用方式

1. 單一資料庫：切換至目標資料庫後執行 EXEC sp_rebuild_index（可選參數 @Rebuild_Fragmentation_Percent = 10 調整閾值）
2. 執行個體所有使用者資料庫：EXEC sys.sp_MSforeachdb 'USE ?; EXEC sp_rebuild_index'

常見錯誤與解決

錯誤 195：'object_schema_name' 不是可識別的內建函數名稱

原因：SQL Server 2005 RTM 版本缺少此函數，需安裝 Service Pack 4 (SP4) 或更新版本即可解決。

補充建議

• 生產環境建議在維護時段（低峰期）執行，並考慮加上 WITH (ONLINE = ON) 選項（Enterprise 版支援）以避免鎖定影響業務。
• 定期配合 UPDATE STATISTICS 或 sp_updatestats 更新統計資訊，確保查詢優化器有最新資訊。
• 若索引極大，REBUILD 可能導致交易記錄暴增；可改用 REORGANIZE 處理輕度碎片（5%~30%），REBUILD 處理重度碎片（>30%）。