There is no workaround. This is by design. As per Microsoft documentation:
Occurs when a task is waiting to acquire a Schema Share lock. See
Schema Locks for more information.
The SQL Server Database Engine uses schema stability (Sch-S) locks
when compiling and executing queries. Sch-S locks do not block any
transactional locks, including exclusive (X) locks. Therefore, other
transactions, including those with X locks on a table, continue to run
while a query is being compiled. However, concurrent DDL operations,
and concurrent DML operations that acquire Sch-M locks, cannot be
performed on the table.
Here is a repo of what you are seeing and why you are being blocked. I am using the AdventureWorks database https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks and big tables created using a query written by Adam Machanic.
Run this from one of the query windows:
CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate
Open a new window and do a select with NOLOCK.
FROM dbo.bigTransactionHistory WITH (NOLOCK)
Now check wait type/blocking and locks held by each session:
@get_locks = 1
You will see that select with NOLOCK is blocked because of LCK_M_SCH_S lock.
If you see the locks column from the above output and click on the XML you will see that select with NOLOCK is requesting an
Sch-S (Schema stability lock) lock and is waiting because this lock is not compatible with
Here is a Q&A https://dba.stackexchange.com/questions/207433/how-to-avoid-a-select-query-for-holding-a-sch-s-lock where Mike Walsh explains why this is necessary.
General locking information (copied from https://www.sqlskills.com/help/waits/lck_m_sch_s/
- For the complete lock compatibility matrix, see the Books Online page https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)?redirectedfrom=MSDN
- For information on the lock hierarchy, see the Books Online page Lock https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189849(v=sql.105)?redirectedfrom=MSDN
- For information on some of the lock modes, see the Books Online page https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175519(v=sql.105)?redirectedfrom=MSDN .
- For other locking topics, see the Books Online page https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190615(v=sql.105)?redirectedfrom=MSDN .