What is blocking 'SELECT... WITH(NOLOCK)'? 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)
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:


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);

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

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

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