4

I don't see why an index rebuild would require a Sch-M lock on the given index.

4

Schema modification (Sch-M) locks are acquired when any metadata changes occur.

In case of index rebuild (online or offline) a new copy of an index is built, and the old index is dropped. Before dropping an old index, table metadata need to be updated.

So at the final stage of index rebuild Sch-M lock is acuired to replace an index reference in the metadata.

UPDATE

Answer to comment

Adding (not rebuilding!) a new nonclustered index online does not require Sch-M. – Paul White

Ok. Here is my new repro:

use tempdb;
go

create table dbo.t (id int);
insert into dbo.t values (1);
go

begin tran;
create index IX_t on dbo.t (id) with (online = on);

select l.request_mode, 
       l.resource_type, 
       l.resource_subtype, 
       l.resource_associated_entity_id, 
       object_name(p.object_id), 
       p.index_id
from sys.dm_tran_locks l 
     left join sys.partitions p 
        on p.hobt_id = l.resource_associated_entity_id
where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

commit;
go

enter image description here

  • 1
    Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock? – Kimo Jan 31 at 14:31
  • 1
    The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one – sepupic Jan 31 at 14:35
10

The documentation for How Online Index Operations Work says:

SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

* The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.

That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.

Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).

Also related from the documentation:

ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...

From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.

In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:

When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.

Related links:

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.