Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

indexindex-tuningsql server

While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run: 

SELECT Quantity FROM [Production].[TransactionHistory]  WITH(NOLOCK)

while executing a rebuild from mentioned article and observe that it is being delayed until index rebuild finished

What is blocking SELECT ... WITH(NO LOCK) and how to avoid it?

Update:
Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64)
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Best Answer

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

  1. In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:

    USE AdventureWorks2012;
SELECT @@SPID;
BEGIN TRANSACTION;
ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);

  2. In a second window, execute your NOLOCK query (again, make note of the spid):

    SELECT @@SPID;
SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);

  3. In a third window, check:

    SELECT session_id, blocking_session_id, last_wait_type 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id IN (two spids above)
     OR session_id IN (two spids above);

EXEC sp_lock;

    Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...

Related Question