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:
create table dbo.ConcurrencyTest
id int identity(1, 1) not null
constraint PK_ConcurrencyTest_Id primary key clustered,
some_int int not null
insert into dbo.ConcurrencyTest
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):
set some_int = 2
where id = 7;
And if another session attempts to rebuild the clustered index on that table:
alter index PK_ConcurrencyTest_Id
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:
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.