-6

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?

0

1 Answer 1

2

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.

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.