1

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)

1 Answer 1

5

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

5
  • So, "offline" for a rebuild synonym of "exclusively locked"?
    – Fulproof
    Nov 21, 2013 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. Nov 21, 2013 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... Nov 21, 2013 at 4:30
  • I'm interested in ONLINE (having Enterprise Ed.) Can't I simulate it by inserting multiple rebuild statements?
    – Fulproof
    Nov 21, 2013 at 5:09
  • @Fulproof no, because one rebuild will start, then your nolock query will start, and it will get in line right after the first rebuild finishes and before the second one starts. Nov 21, 2013 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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