The Curious Case of… almost online index rebuilds

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I had a comment on my blog a few weeks ago from someone who hadn’t known that online index operations acquire blocking table locks – yes they do!

When I’m teaching the index fragmentation module of our IEPTO1 class, I like to joke that the marketing team back in 2005 called them ‘online index operations’ because ‘almost online index operations’ wasn’t a good marketing term :-)

Using an online index rebuild as an example, here are the basic operations that happen:

  • Acquire a table S (Share) lock. This makes sure that there are no open transactions making changes to the table. This is necessary because the next step is to…
  • Create the empty new index and start a versioning scan of the old index. It doesn’t enable snapshot isolation, but it uses the same underlying mechanism to preserve a point-in-time view (for want of a better word) of the old index.
  • Cause all query plans that affect the old index to recompile, so changes are made to both the old and the new index.
  • Downgrade the table S lock to an IS (Intent-Share) lock, which remains in place for the duration of the operation. This allows read and writes to the table.
  • Populate the new index. Any changes to the old index are also made to the new index, with logic in place to cope with pathological problems.
  • Acquire a table Sch-M (Schema Modification) lock, which is not compatible with any other lock, so no readers or writers can be active on the table.
  • Swap the new index for the old one, queue the old index for deferred drop, recompile all query plans.
  • Drop the table lock

So you can see there are two points at which an online operation must acquire a table lock that could cause blocking. This can be mitigated somewhat from SQL Server 2014 onward using the WAIT_AT_LOW_PRIORITY option, but it’s still a necessity.

One thought on “The Curious Case of… almost online index rebuilds

  1. Same as “NOLOCK” should be “AlmostNOLOCK”, and “AlwaysOn” should be “AlmostAlwaysOn” :) Marketing, sheesh.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.