How to avoid a select query for holding a Sch-S lock

Question

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.

Answer 1

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.

Answer 2

Agree with StrayCatDBA and their answer.

Schema Stability locks are one of those necessary parts of SQL Server. While you can absorb a dirty read (or any of the problems that go with uncommitted reads), you really can't absorb the impact of an underlying schema changing on you mid query. Really, SQL couldn't handle it either. So that is a lock you can't get rid of.

I feel, though, like there may be a deeper underlying question here. Why do I say this? Because in working with SQL Server for 20 years now, I've been frustrated at times by locking involving SCH-S or SCH-M locks but I've never found myself saying, "Oh! If only I could remove these pesky schema stability or schema modification locks, all would be solved!!"

Two selects each holding an SCH-S lock will not hurt you queries. An update won't block a select with because it is holding an SCH-S lock, it would block it if you were in read committed (the default) isolation level and the locks needed at the table or page level are incompatible with the ones for the update, for instance.

So I would turn the question back on you and ask what you are doing to be burned by the SCH-S locks. Your SCH-S lock isn't normally causing a problem unless something else is trying to truncate a table, alter a table, alter a column, etc. Or during index rebuilding operations.

So you could either have a situation where you need to (1) analyze DML happening against your table during production workloads; (2) or a situation where another lock type is the real issue; (3)or a situation where maybe you are truly doing something that somehow makes a block along this lock type make sense.

For those I would probably start down one of the paths:

1. Figure out why you are doing DML live on a table you are trying to read from live and stop doing that or come up with a better process of offlining those reads someplace else.
2. This is what I am suspecting. Try and find your blocking SQL (Try using SP_Whoisactive and catch a block - what is the blocking session_id? what is it doing? Look at that and see if that is really your issue). Deal with the true blocking cause.
3. If this really expected behavior. You'll have to sort out some other way to grab data to a copy of the table. Lots of approaches here. Streaming it to two tables, ETL, Readable AG secondary, Replication, etc. But be warned - all of these techniques will involve a SCH-S lock. In short - the ONLY operation that should block a SCH-S held lock would be a SCH-M lock. So the only way this would be happening would be someone is modifying a table's STRUCTURE whilst you are querying the table's data.

My guess is you are really on a goose chase, and the issue is #2, though.

UPDATE - On re-reading your question, I'm more convinced you are likely either A.) in a fine situation with no issues and just being proactive/worried or B.) your application is doing a really high frequency of index or table modifications in the middle of the production day. I am more convinced you are fine here. And the answer of copying all of the data out to just avoice a SCH-S lock doesn't really fit well - because you'll likely have more performance issues copying that data out and keeping it up to date and generate SCH-S locks there.

The real answer would be to look at what the operations are in the vendor's app that is causing the SCH-M locks which are blocked by your SCH-S locks. Perhaps looking at ONLINE index rebuilds if enterprise, perhaps less frequency, etc.

Answer 3

I came across the same situation where I needed SELECT query on a partition table not to take sch-s lock so that a other TRUNCATE query on different partition can continue it work. Surprisingly it turns out that if I run my select query in small batches then sch-s lock is reduced to 99.99%. which was perfect for my situation as Truncate just needed few milliseconds to complete. but with the full SELECT on whole partition the sch-s was held for the whole duration of select and hence TRUNCATE was blocked for whole duration of select.