5

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.

  • 5
    LCK_M_SCH_M means something has a schema lock that is blocking your process. If you run sp_who2, you should see a blocking process that is preventing the index rebuild from proceeding. – Mike Fal Apr 12 '13 at 2:58
  • @amin: Any news for this? have you seen the comment and the answers? – Marian May 17 '13 at 13:26
7

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:

enter image description here

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:

enter image description here

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.

  • This is a good answer and a great example! +1 – Marian May 17 '13 at 13:12
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.