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