A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Another short one today as I'm still teaching a class. (Ok – I'm actually writing this at lunchtime on 4/7/10 – and it turned out to be a little longer than I thought…)

Myth #8: Online index operations do not acquire locks.

FALSE

Online index operations are not all unicorns and rainbows (for information about unicorns and rainbows – see http://whiteboardunicorns.com/ – safe for work).

Online index operations acquire short-term locks at the beginning and end of the operation, which can cause significant blocking problems.

At the start of the online index operation, a shared (S lock mode) table lock is required. This lock is held while the new, empty index is created; the versioned scan of the old index is started; and the minor version number of the table schema is bumped by 1.

The problem is, this S lock is queued along with all other locks on the table. No locks that are incompatible with an S table lock can be granted while the S lock is queued or granted. This means that updates are blocked until the lock has been granted and the operation started. Similarly, the S lock cannot be granted until all currently executing updates have completed, and their IX or X table locks dropped.

Once all the setup has been done (this is very quick), the lock is dropped, but you can see how blocking of updates can occur. Bumping the minor version number of the schema causes all query plans that update the table to recompile, so they pick up the new query plan operators to maintain the in-build index.

While the long-running portion of the index operation is running, no locks are held (where 'long' is the length of time your index rebuild usually takes).

When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.

There's a blocking problem here that's similar to when the S lock was acquired at the start of the operation – but this time, no read or write operations can start while the schema-mod lock is queued or granted, and it can't be granted until all currently running read and write activity on the table has finished.

Once the lock is held, the allocation structures for the old index are unhooked and put onto the deferred-drop queue, the allocation structure for the new index are hooked into the metadata for the old index (so the index ID doesn't change), the table's major version number is bumped, and hey presto! You've got a sparkly new index.

As you can see – plenty of potential for blocking at the start and end of the online index operation. So it should really be called 'almost online index operations', but that's not such a good marketing term…

You can read more about online index operations in the whitepaper Online Indexing Operations in SQL Server 2005.

8 thoughts on “A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations

  1. Great post. Thanks. If this implies a ‘long’ time between online index start (S lock) and stop (SCH_M) operations, where ‘long’ is implied or required, then I start thinking about how to successfully schedule multiple of these operations.

  2. Hi Paul,

    Please help us in grasping below point
    =======================================
    Online Index Rebuild Preparation Phase

    Index metadata is created==> Here we could also create metadata at the end or Final Phase?
    DML plans are recompiled==> Are All plans recompiled and what’s the link to version number of object and how to check version number of object?

    an internal-only, per-object, online-version number is incremented before making this new index visible to concurrent connections. The version number change causes all cached DML plans for that table or indexed view to recompile so that the new index, as well as the original index, are maintained going forward. Remember that an (S) lock was acquired at the beginning of the preparation phase. While this lock allows read operations, write operations are blocked, so no DML queries are actually running at this point. Select operations are not affected by the version change because these queries access the original index and simply ignore the “in-build” index”

    1. Q1: No, as the end phase swaps in the new index into the existing metadata. Not possible to do it earlier.
      Q2: No, just plans that update the index being rebuilt. There is no link – there’s no supported way to see the version number.

  3. Hello. I’m trying to add some columns to a partitioned index (using DROP_EXISTING = ON, ONLINE = ON on SQL 2012) and get a couple of minutes blocking at the beginning of the operation. If I have the same data in a single partition table the same test harness that is generating inserts doesn’t have the same difficulties and any blocking is indeed very short.

    Would you be able to explain the differences? What is happening behind the scene to cause such a difference?

    1. The two operations should be the same and both require a schema-modification lock. Could just be a timing thing. If it’s reproable, shoot me an email with the complete repro for both cases please.

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.