Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
1  
But but but but ... TURBO BUTTON –  zlatan Nov 21 '13 at 4:13
    
So, "offline" for a rebuild synonym of "exclusively locked"? –  Fulproof Nov 21 '13 at 4:25
    
@Fulproof Yes, essentially. However to fully simulate what you're seeing and to test it with an ONLINE rebuild, you need to be sure the rebuild takes long enough that you can test it reliably. The transaction does throw extra wrenches in there that help make it easy to illustrate the problem without having to simulate a large rebuild, however the transaction itself will also cause a blocking issue, because the locks from the rebuild do not get released even after the rebuild is done. –  Aaron Bertrand Nov 21 '13 at 4:29
    
I'm at a conference heading out the door so I don't have the ability to simulate a large enough online rebuild that doesn't block read queries (with or without NOLOCK), but that's essentially what you are paying for with Enterprise Edition - the ability to read a snapshot of the data that is also getting rebuilt in the background. If you have Enterprise, Developer, or Evaluation Edition, you can try out these things in the meantime... –  Aaron Bertrand Nov 21 '13 at 4:30
    
I'm interested in ONLINE (having Enterprise Ed.) Can't I simulate it by inserting multiple rebuild statements? –  Fulproof Nov 21 '13 at 5:09
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.