Suspended in index rebuild

Question

In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job.

In monitoring SQL task:

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

Thanks in advance for any helpful info.

Answer 1

The reason that your index rebuild isn't completing is because of the LCK_M_SCH_M wait type. What happens when you try to rebuild an index, a Sch-M lock is requested on the object that you're trying to rebuild.

Please see this below chart for lock compatibility:

As you can see here, a Sch-M lock has a conflict with almost every locking scenario (shared, exclusive, update, schema stability, etc.).

Here's a small example showing what may be happening in your environment. To create the test object in a test database:

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

Now if one session is executing a query, and it keeps the lock open (I'm using an update query and not committing the transaction):

use TestDB;
go

begin tran;

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

--commit tran;

And if another session attempts to rebuild the clustered index on that table:

use TestDB;
go

alter index PK_ConcurrencyTest_Id
on dbo.ConcurrencyTest
rebuild;
go

It's going to be blocked by the initial UPDATE query. We can see this through a little diagnostic query below:

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;

My output looks like the following:

As you can see here, my ALTER INDEX ... REBUILD command is being blocked by the UPDATE query. The request lock is the Sch-M lock that is required on the OBJECT in order for this operation to complete.

Due to this concurrency conflict, it is advisable to schedule your index maintenance (as well as other maintenance tasks) during a window where there is little to no user load.

Answer 2

We typically time our larger index maintenance operations to take place in midnight when there's minimal business-use. Without knowing the business-critical constraints on the task, you might want to force the database into single_user mode in case there are too many non-critical queries being run against the database during the maintenance period. Assuming this won't cause other problems of course, depends on the system as a whole.