I am looking for an option to avoid Sch-S locks when running a Select query.
I have a database which is controlled by an application written by others as well as my own application. One of the tables has millions of rows. I don't have any issue with dirty reads etc. but I don't my select query to lock the other application indexing or modification queries that have to wait due to Sch-S locks from my query.
I tried to set isolation level snapshot before calling my query but that didn't make any difference either WITH NOLOCK option that still acquires an Sch-S lock.
I am looking for an option to run a select on a table without acquiring Sch-S or any kind of lock, maybe if we can quickly have snapshot/view or temp copy (will it work as it has millions of rows of data?) of the table without acquiring any kind of lock at all.
Best Answer
A Sch-S lock is a Schema stability lock. It is taken to ensure that the structure of the table doesn't change. This includes adding/removing columns, etc. NOLOCK hints and isolation levels affect the locking and versioning of the data in the table, not the structure of the table itself.
Even an online index operation will need to briefly take Sch-S and Sch-M locks. You cannot avoid this for indexing or schema modification queries.