I don't see why an index rebuild would require a Sch-M lock on the given index.
Schema modification (
Sch-M) locks are acquired when any metadata changes occur.
In case of
index rebuild (
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
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
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
Also related from the documentation:
ALTER TABLEacquires 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.
- The Sch-M lock is Evil by Michael J Swart