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.

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:

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:

@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