Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M).
Does an offline index rebuild of clustered or non-clustered index also block user's SELECT/UPDATE/INSERT/DELETE queries?
I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M).
You seem to have this backwards. Any operation that changes system metadata requires a schema modification (sch-m) lock at some stage. Regular user queries require at least schema stability (sch-s) even when running under read uncommitted isolation. See schema locks in the documentation.
Does an offline index rebuild of clustered or non-clustered index also block user's SELECT/UPDATE/INSERT/DELETE queries?
The documentation is not as clear as it could be about this in all places, but this section is relatively clear (emphasis added):
An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table during the operation.
