暂停索引重建

Question

在Server 2012中，索引重建工作需要很长时间(最多8小时)。但是，连一个索引重建都没有完成，所以我停止了索引作业。

在监视SQL任务时：

state = SUSPENDED
, comment = ALTER INDEX
, APPLICATION name = Microsoft SQL server Management Studio
, Query Wait = LCK_M_SCH_M 
, Head Blocker = object lock 
lock Partition = 15 
objid=585105175 
subresource=FULL 
dbid=5 
id=lockaa7aae200 
mode=Sch-S 
associatedObjectId=585105175 1386

谢谢您提供任何有用的信息。

Answer 1

索引重建未完成的原因是由于LCK_M_SCH_M等待类型。当您尝试重新生成索引时，会对您试图重建的对象请求一个Sch-M锁。

为了获得锁兼容性，请参见下面的图表：

正如您在这里看到的，Sch-M锁与几乎所有锁定场景(共享、排他、更新、模式稳定性等)都有冲突。

这里有一个小例子，展示了您的环境中可能发生的事情。要在测试数据库中创建测试对象，请执行以下操作：

use TestDB;
go

create table dbo.ConcurrencyTest
(
    id int identity(1, 1) not null
        constraint PK_ConcurrencyTest_Id primary key clustered,
    some_int int not null
        default 1
);
go

insert into dbo.ConcurrencyTest
default values;
go 100

现在，如果一个会话正在执行一个查询，并且保持锁处于打开状态(我使用的是update查询，而不是提交事务)：

use TestDB;
go

begin tran;

    update dbo.ConcurrencyTest
    set some_int = 2
    where id = 7;

--commit tran;

如果另一个会话试图重新构建该表上的聚集索引：

use TestDB;
go

alter index PK_ConcurrencyTest_Id
on dbo.ConcurrencyTest
rebuild;
go

它将被初始的UPDATE查询所阻塞。我们可以通过下面的一个小诊断查询来了解这一点：

select
    l.resource_type,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    st.text as blocked_sql_text,
    r.blocking_session_id,
    stb.text as blocking_sql_text
from sys.dm_tran_locks l
inner join sys.dm_exec_connections c
on l.request_session_id = c.session_id
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
inner join sys.dm_exec_connections cb
on r.blocking_session_id = cb.session_id
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
cross apply sys.dm_exec_sql_text(cb.most_recent_sql_handle) stb
where l.resource_database_id = db_id('TestDB')
and l.request_status = 'WAIT'
and r.blocking_session_id is not null
and r.blocking_session_id > 0;

我的输出如下所示：

正如您在这里看到的，我的ALTER INDEX ... REBUILD命令正被UPDATE查询所阻塞。请求锁是为了完成此操作而在Sch-M上需要的OBJECT锁。

由于这种并发冲突，建议在用户负载较少或没有加载的窗口中安排索引维护(以及其他维护任务)。

Answer 2

通常情况下，我们的大型索引维护操作将在午夜时分进行，此时的业务使用很少。在不了解任务上的业务关键约束的情况下，您可能希望强制数据库进入single_user模式，以防在维护期间对数据库运行太多非关键查询。当然，假设这不会导致其他问题，则取决于整个系统。