What is blocking 'SELECT... WITH(NOLOCK)'?

Question

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)

Answer 1

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...