DECLARE @sql NVARCHAR(MAX);
DECLARE @table_name NVARCHAR(255);
DECLARE @index_name NVARCHAR(255);
DECLARE @fragmentation FLOAT;
DECLARE @schema_name NVARCHAR(255);

— 设置数据库上下文
IF @database_name IS NOT NULL
BEGIN
SET @sql = ‘USE ‘ + QUOTENAME(@database_name) + ‘;’;
EXEC sp_executesql @sql;
END;

— 游标遍历需要维护的索引
DECLARE index_cursor CURSOR FOR
SELECT
SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
i.name AS index_name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(),
NULL,
NULL,
NULL,
‘DETAILED’
) ps
JOIN sys.tables t ON ps.object_id = t.object_id
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > @fragmentation_threshold_reorganize
AND i.name IS NOT NULL
AND t.is_ms_shipped = 0
ORDER BY ps.avg_fragmentation_in_percent DESC;

OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @schema_name, @table_name, @index_name, @fragmentation;

WHILE @@FETCH_STATUS = 0
BEGIN
— 根据碎片程度选择维护方式
IF @fragmentation > @fragmentation_threshold_rebuild
BEGIN
— 重建索引
SET @sql = ‘ALTER INDEX ‘ + QUOTENAME(@index_name) + ‘ ON ‘ + QUOTENAME(@schema_name) + ‘.’ + QUOTENAME(@table_name) + ‘ REBUILD WITH (‘;
IF @online_rebuild = 1
BEGIN
SET @sql = @sql + ‘ONLINE = ON, ‘;
END;
SET @sql = @sql + ‘FILLFACTOR = 80, SORT_IN_TEMPDB = ON, MAXDOP = 4);’;
END
ELSE
BEGIN
— 重组索引
SET @sql = ‘ALTER INDEX ‘ + QUOTENAME(@index_name) + ‘ ON ‘ + QUOTENAME(@schema_name) + ‘.’ + QUOTENAME(@table_name) + ‘ REORGANIZE;’;
END;

— 执行维护操作
PRINT ‘Executing: ‘ + @sql;
EXEC sp_executesql @sql;

FETCH NEXT FROM index_cursor INTO @schema_name, @table_name, @index_name, @fragmentation;
END;

CLOSE index_cursor;
DEALLOCATE index_cursor;

— 更新统计信息
PRINT ‘Updating statistics…’;
EXEC sp_updatestats;

PRINT ‘Index maintenance completed successfully.’;
END;
GO

— 步骤2：创建作业执行索引维护
EXEC dbo.sp_add_job @job_name = N’AutomatedIndexMaintenance’;
EXEC dbo.sp_add_jobstep
@job_name = N’AutomatedIndexMaintenance’,
@step_name = N’Execute Index Maintenance’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.usp_IndexMaintenance @database_name = ”fgedudb”, @online_rebuild = 1;’,
@database_name = N’fgedudb’;
EXEC dbo.sp_add_schedule
@schedule_name = N’DailyIndexMaintenance’,
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 020000;
EXEC dbo.sp_attach_schedule
@job_name = N’AutomatedIndexMaintenance’,
@schedule_name = N’DailyIndexMaintenance’;
EXEC dbo.sp_add_jobserver
@job_name = N’AutomatedIndexMaintenance’;
GO

— 步骤3：测试索引维护存储过程
EXEC dbo.usp_IndexMaintenance @database_name = ‘fgedudb’, @online_rebuild = 1;
GO

— 步骤4：监控索引维护作业
— 查看作业执行历史
SELECT
j.name AS job_name,
h.run_date,
h.run_time,
h.run_duration,
h.status,
h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name = ‘AutomatedIndexMaintenance’
ORDER BY h.run_date DESC, h.run_time DESC;
GO

执行结果：