重建联机索引，而不对其他请求构成风险

Question

我们有一个SQLServer2012EnterpriseEdition数据库，其索引片段非常快。

我们使用一个维护计划，每天重新组织这些索引，并每周重新构建它们。重建将在线进行，并使用tempdb (功能)。

但是，在重建的2分钟内，与数据库的其他连接会受到影响。我们有好几次暂停或者死锁。有时，重建本身也会因陷入僵局而失败。

重建失败没问题，我们可以再试一次。然而，其他连接超时甚至花费很长时间是非常麻烦的。

是否有一种方法可以以较低的优先级运行重建，或者如果当前处理资源不足以进行联机重建，则会自动失败？

此数据库没有停机窗口，因此不能脱机重新生成索引。有没有办法在不影响其他请求的情况下在线重建索引？

Answer 1

有几个选项可以帮助减少您所看到的问题：

/* set the LOCK_TIMEOUT value to the maximum number of milliseconds 
   you want to wait for the ALTER INDEX REBUILD to start
*/
SET LOCK_TIMEOUT 100;

/* this tells SQL Server our ALTER INDEX REBUILD statement should
   be the deadlock victim
*/
SET DEADLOCK_PRIORITY LOW;

/* Rebuild the index */
ALTER INDEX MyIndex 
ON MySchema.MyTable 
REBUILD WITH (
    ONLINE = ON
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

我知道您有Server 2012，但是对于Server 2014+，可以使用WAIT_AT_LOW_PRIORITY选项告诉Server允许在联机索引生成操作等待获取其架构修改锁时继续进行其他会话。

ALTER INDEX MyIndex 
ON MySchema.MyTable 
REBUILD WITH (
    ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

来自MSDN Server联机丛书：

为了执行联机索引重建的DDL语句，必须完成在特定表上运行的所有活动阻塞事务。当联机索引重新生成执行时，它会阻止准备在此表上开始执行的所有新事务。虽然联机索引重新生成的锁持续时间非常短，但等待给定表上所有打开的事务完成并阻止新事务的启动，可能会严重影响吞吐量，导致工作负载减慢或超时，并大大限制对基础表的访问。WAIT_AT_LOW_PRIORITY选项允许DBA管理在线索引重建所需的S锁和Sch-M锁，并允许它们选择3种选项之一。在所有3种情况下，如果在等待时间(MAX_DURATION =n 分钟)期间没有阻塞活动，则无需等待就立即执行联机索引重建，并完成DDL语句。