全表所有索引重建
-- 重建表上所有索引 (含 PK、XML、空間索引)
ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON, MAXDOP = 4, FILLFACTOR = 80);
-- 僅重建非叢集索引 (保留叢集索引順序)
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'ALTER INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' REBUILD WITH (ONLINE=ON, MAXDOP=4);' + CHAR(10)
FROM sys.indexes
WHERE object_id = OBJECT_ID('Orders') AND index_id > 1 AND type_desc <> 'HEAP';
EXEC sp_executesql @sql;
Step 3:統計資料同步更新 (比重建更關鍵)
-- 重建索引會自動以 FULLSCAN 更新該索引統計資料
-- 但非索引列統計、手動建立的統計需單獨更新
-- 更新表上所有統計 (採樣)
UPDATE STATISTICS Orders WITH RESAMPLE;
-- 關鍵表全掃描更新 (維護窗口執行)
UPDATE STATISTICS Orders WITH FULLSCAN, ALL, NORECOMPUTE;
-- 啟用自動更新統計 (資料庫級)
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON; -- 非同步更新,避免查詢等待
生產級自動化維護腳本 (Ola Hallengren 風格簡化版)
CREATE PROCEDURE dbo.IndexMaintenance
@FragmentationLow INT = 5,
@FragmentationHigh INT = 30,
@FillFactor INT = 80,
@MaxDOP INT = 4,
@Online BIT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @msg NVARCHAR(4000);
-- 建立暫存表收集需處理索引
CREATE TABLE #IdxMaint (
SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME,
IndexID INT, FragPct FLOAT, PageCnt BIGINT, Action VARCHAR(20)
);
INSERT INTO #IdxMaint
SELECT
OBJECT_SCHEMA_NAME(ips.object_id),
OBJECT_NAME(ips.object_id),
i.name,
ips.index_id,
ips.avg_fragmentation_in_percent,
ips.page_count,
CASE
WHEN ips.avg_fragmentation_in_percent >= @FragmentationHigh THEN 'REBUILD'
WHEN ips.avg_fragmentation_in_percent >= @FragmentationLow THEN 'REORGANIZE'
ELSE 'NONE'
END
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
WHERE ips.page_count > 1000
AND ips.avg_fragmentation_in_percent >= @FragmentationLow
AND i.is_disabled = 0 AND i.is_hypothetical = 0;
-- 執行維護
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT SchemaName, TableName, IndexName, Action FROM #IdxMaint WHERE Action <> 'NONE';
OPEN cur;
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @Action;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg = '[' + CONVERT(VARCHAR, GETDATE(), 120) + '] ' + @Action + ' ' + @SchemaName + '.' + @TableName + '.' + @IndexName;
RAISERROR(@msg, 0, 1) WITH NOWAIT; -- 即時輸出到 Messages
IF @Action = 'REBUILD'
SET @sql = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' REBUILD WITH (ONLINE = ' + CASE @Online WHEN 1 THEN 'ON' ELSE 'OFF' END + ', MAXDOP = ' + CAST(@MaxDOP AS VARCHAR) + ', FILLFACTOR = ' + CAST(@FillFactor AS VARCHAR) + ');';
ELSE
SET @sql = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REORGANIZE;';
BEGIN TRY
EXEC sp_executesql @sql;
SET @msg = '[' + CONVERT(VARCHAR, GETDATE(), 120) + '] SUCCESS';
END TRY
BEGIN CATCH
SET @msg = '[' + CONVERT(VARCHAR, GETDATE(), 120) + '] FAILED: ' + ERROR_MESSAGE();
END CATCH
RAISERROR(@msg, 0, 1) WITH NOWAIT;
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @Action;
END
CLOSE cur; DEALLOCATE cur;
DROP TABLE #IdxMaint;
END;
Agent Job 排程部署
-- 每週日 02:00 執行 (維護窗口)
EXEC msdb.dbo.sp_add_job @job_name = 'Weekly_Index_Maintenance';
EXEC msdb.dbo.sp_add_jobstep @job_name = 'Weekly_Index_Maintenance',
@step_name = 'Run Index Maintenance',
@subsystem = 'TSQL',
@command = 'EXEC dbo.IndexMaintenance @FragmentationLow=5, @FragmentationHigh=30, @FillFactor=80, @MaxDOP=4, @Online=1;',
@database_name = 'YourDB';
EXEC msdb.dbo.sp_add_jobschedule @job_name = 'Weekly_Index_Maintenance',
@freq_type = 8, @freq_interval = 1, @active_start_time = 020000; -- Weekly Sunday 02:00
EXEC msdb.dbo.sp_add_jobserver @job_name = 'Weekly_Index_Maintenance';
進階:統計資料獨立維護作業
CREATE PROCEDURE dbo.StatisticsMaintenance
AS
BEGIN
SET NOCOUNT ON;
-- 1. 更新全資料庫統計 (採樣) - 每日低峰
EXEC sp_updatestats; -- sp_updatestats 僅更新需更新的統計 (rowmodctr 閾值)
-- 2. 關鍵大表全掃描 - 每週
UPDATE STATISTICS dbo.Orders WITH FULLSCAN, ALL;
UPDATE STATISTICS dbo.Customers WITH FULLSCAN, ALL;
-- ... 其他核心表
END;
監控與告警整合
- 作業失敗告警: Agent Job 通知 → Email / Teams / 企業微信
- 碎片率趨勢: 將
dm_db_index_physical_stats 歷史寫入基線表,繪製趨勢圖
- 維護窗口超時: 設定 Job Step 逾時 (如 4 小時),超時自動取消並告警
- TempDB 壓力: 重建大索引時 TempDB 可能暴增,預先擴大 TempDB、監控版本存儲
版本與版本差異提醒