要超时或跳过Server索引重新生成语句(如果花费的时间太长)

Question

当在大型和高度支离破碎的数据库上一夜之间运行索引重建脚本时，我们经常会遇到停止作业的索引，因为它们似乎从未完成它们的重新构建。作业已经停止，早上我们必须手动取消语句，永远等待回滚，导致停机。我们可以补救这些偶尔的索引，我手动删除和重新创建。

如果没有什么进展，是否有一种方法可以自动超时索引重建语句，以便脚本可以继续执行下一个重新构建语句？

我们的脚本的形式如下：

ALTER INDEX ALL ON [Table1] REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON [Table2] REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON [Table3] REBUILD WITH (FILLFACTOR = 90)..

Answer 1

首先，我建议您使用一个免费脚本/工具来管理索引重建。其中最著名的是奥拉·哈伦 DB维护套件。这使您可以控制索引在尝试重新组织或重建之前必须是多分段的。

第二，如果你仍然有问题的过程似乎挂起，试着创造两个就业机会。第一个任务将实际运行索引重建过程。第二个任务将确保第一个作业的运行时间不会超过必要时间。

让我们称第一个作业为“索引主板作业”。不要安排这项工作。

当您希望运行索引维护时，将安排第二个作业，并将执行以下脚本：

DECLARE @jobname nvarchar(128) = N'Index Maint Job';
DECLARE @proc_start_date datetime = GETDATE();

EXECUTE sp_start_job @job_name = @jobname;

WAITFOR DELAY '003:00:00';


IF (EXISTS (SELECT 1
              FROM sysjobactivity ja
                     INNER JOIN sysjobs j ON (ja.job_id = j.job_id)
             WHERE j.name = @jobname
               AND start_execution_date >= @proc_start_date
               AND stop_execution_date IS NULL
           )
   )
BEGIN
    Print N'Aborting job ''' + @jobname + N'''...';
    EXECUTE sp_stop_job @job_name = @jobname;
END
ELSE PRINT N'Job ''' + @jobname + N''' finished.';

这会启动作业，等待它得到您认为足够完成的时间，检查它是否仍在运行，如果仍在运行，则停止它。

(我最初计划使用TRY ... CATCH来捕获如果作业没有运行而您试图停止的错误；但是，这是TRY ... CATCH无法处理的Server代理错误。因此，我们检查该作业是否仍在运行。)

这里需要知道的关键元素是，sp_start_job不等待作业完成；它在作业启动后返回。因此，该作业在这一点上再次控制了自身，可以等待多久(当前设置WAITFOR DELAY '003:00:00'为3小时)，以查看该作业是否仍在运行，如果仍在运行，则停止它。

FYI:正如其他人已经注意到的，索引重组是逐渐发生的；我有一个大型的支离破碎的PK索引来尝试分解，并且计划用Ola的脚本来实现这一点，以确保流程不会运行太长时间。

Answer 2

为什么重建表上的所有索引..。相反，选择索引分段阈值，只触及页面数> 5K (例如)具有较高分段计数的索引？使用Ola索引维护，例如@TimeLimit和@LockTimeout参数或走狗再指数 (两者都可以自由使用--我使用Ola的索引，而不是minion重新索引解决方案)。

有关更多详细信息，请参阅我的回答。