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:

, comment = ALTER INDEX
, APPLICATION name = Microsoft SQL server Management Studio
, Query Wait = LCK_M_SCH_M 
, Head Blocker = object lock 
lock Partition = 15 
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, 2013 at 2:58
  • 1
    @amin: Any news for this? have you seen the comment and the answers?
    – Marian
    May 17, 2013 at 13:26

2 Answers 2


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;

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

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;

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;

alter index PK_ConcurrencyTest_Id
on dbo.ConcurrencyTest

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

    st.text as blocked_sql_text,
    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, 2013 at 13:12
  • I know this is so old but I am stuck with an Alter Index Rebuild like this. I have had an Alter Index Rebuild running for 6+ hours. and the status of the Alter Index process is “suspended”. It also has a wait type of LCK_M_SCH_M and it appears there are 100 or so other processes being blocked by the SPID of the Rebuild. Is there a way to abort the Rebuild or will it eventually finish? I cant tell if its even doing anything. by the way the database is ~40GB. May 13, 2023 at 1:32

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 and acknowledge you have read our privacy policy.

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