The maintenance plan was rebuilt with every best-practice checkbox ticked: ONLINE = ON, run at 6 AM Saturday before the traffic ramp. What actually happened at 6:04 was that the checkout service started timing out. The rebuild of the orders table's clustered index had requested its initial schema modification lock, found a nine-minute analytics report holding the table, and joined the lock queue behind it — and because a Sch-M request blocks every new lock request behind it, the four hundred queries a second that arrived after 6:04 piled up in a queue nobody had planned for. By the time the report finished, the rebuild got its lock, held it for its moment, and the queue drained — but the incident channel had already named the maintenance window as the outage.
ONLINE = ON is real engineering, not marketing. It is also badly named, because it describes the middle of the operation and stays silent about the two ends. This is how the phases actually work, what the blocking surface is, the options that make the ends survivable, and when the honest answer is to skip online and go fast. Everything here applies to SQL Server 2016 through 2022, with resumable rebuilds from 2017 onward called out where they land.
How does an online rebuild actually work?
In three phases, and only the middle one is lock-free. Phase one is brief: the engine takes a short-lived schema modification lock — Sch-M — to register the operation and create the new index structure alongside the old one. Phase two is the long one: the engine scans the old index, builds the new one, and keeps a mapping index so that concurrent inserts, updates, and deletes against the old structure get applied to the new one as they happen. User queries keep running against the old index throughout, holding their ordinary locks, and the rebuild proceeds in parallel. That phase is what "online" means, and it is genuinely concurrent — the version store may carry the row versions needed to keep the scan consistent, which is worth remembering if tempdb is already under pressure.
Phase three is the other brief one: to swap the new structure in as the live index, the engine takes Sch-M again for the final metadata switch. Two Sch-M locks, one at each end, each held for a short time — but the duration of the hold is not the problem. The problem is queue position.
Why does a "short" Sch-M lock take the table down?
Because the Sch-M request itself is a poison pill for the lock queue behind it. SQL Server's lock manager is fair: a request for a schema modification lock that cannot be granted immediately — because the nine-minute report still holds a schema stability lock, which every compiled query takes — waits in the queue, and every subsequent lock request, including Sch-S and ordinary shared locks from brand-new queries, queues behind it. The rebuild does not have to hold anything to cause the outage. Its mere intent to take Sch-M creates a wall, and all traffic piles against the wall until the report releases. That is exactly the shape my blocking chain notes describe for LCK_M_ pile-ups, with the lead blocker being an innocent report and the mid-chain victim being your maintenance job.
The end-phase version is worse in one way: after hours of building, the rebuild waits again for Sch-M to commit the swap, and now it is behind whatever the table's current traffic is doing, with the new index fully built and the mapping index consuming resources the whole time. An online rebuild that cannot get its final lock can sit at ninety-nine percent complete, blocking new queries, for as long as the longest transaction on the table runs.
What are WAIT_AT_LOW_PRIORITY and the abort options?
They are the pressure valve for exactly this, available since SQL Server 2014 and criminally underused. Instead of joining the normal lock queue and walling off the table, the rebuild can wait in a separate low-priority queue — new queries jump ahead of it and keep flowing — and after a timeout you choose what gives up:
ALTER INDEX IX_Orders_OrderDate ON dbo.Orders
REBUILD WITH
(
ONLINE = ON
(
WAIT_AT_LOW_PRIORITY
(
MAX_DURATION = 10 MINUTES,
ABORT_AFTER_WAIT = BLOCKERS
)
)
);The three ABORT_AFTER_WAIT values are the decision. NONE means the rebuild gives up after ten minutes and the operation fails — safe for traffic, annoying for the operator who has to retry. SELF is the same in effect for this syntax. BLOCKERS is the aggressive one: after the wait expires, the engine kills the sessions blocking the rebuild so it can proceed. That is the right answer for a maintenance window where the rebuild is the priority and a stray report can be re-run — and it is exactly the wrong answer to leave in a script that might run outside the window, because it will cheerfully kill production queries to make way for index maintenance. Choose per operation, not per organization. My maintenance jobs use BLOCKERS inside the declared window and NONE as the default everywhere else, and the window definition lives in the job, not in my memory.
When should I use a resumable rebuild instead?
When the index is large enough that an interruption costs hours, which on big tables is most of the time. Resumable online rebuilds, introduced in SQL Server 2017, let the operation pause and resume — either because you paused it deliberately or because something interrupted it, up to and including a failover:
ALTER INDEX PK_Ledger ON dbo.Ledger
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240 MINUTES);
-- pause it when the morning traffic ramp starts:
ALTER INDEX PK_Ledger ON dbo.Ledger PAUSE;
-- resume it the following night:
ALTER INDEX PK_Ledger ON dbo.Ledger RESUME;MAX_DURATION is the feature's quiet superpower: set it and the rebuild self-pauses after that many minutes, so a four-hour index rebuild becomes four one-hour slices that fit into four maintenance windows, with normal traffic unaffected in between. The costs are real but modest: the partially built new index and its mapping structures persist while paused, so the table carries extra space and extra write maintenance until the operation completes or aborts — do not leave a rebuild paused for a month. sys.index_resumable_operations shows what is in flight, its state, and its percent complete, which is also your progress view during the run, since percent_complete in dm_exec_requests is unreliable for rebuilds. Resumable plus WAIT_AT_LOW_PRIORITY stack together, and on the largest tables that combination is the whole answer.
When is the honest answer to just go offline?
More often than the online-by-default instinct suggests. An offline rebuild takes Sch-M once, holds it for the duration, and is substantially faster because it skips the mapping index, the double-write maintenance, and the version store traffic. For a table that is genuinely idle during the window — an archive table, a reporting table loaded at night — offline finishes sooner, logs less, and carries zero queue risk beyond its own duration. The 6:04 incident above happened to a table that was never idle; the right fix there was the low-priority wait, not a different online-ness. The decision rule I use: if any production query can touch the table during the operation, go online with WAIT_AT_LOW_PRIORITY; if nothing can, go offline and take the speed. Either way, the Sch-M queue behavior is the thing to respect, because it is the part of the operation the word "online" does not cover.
And the meta-question, from the same maintenance philosophy as my index maintenance reality check: before scheduling any rebuild, confirm the index is fragmented in a way that hurts and that a rebuild is the right response — a reorganize, a statistics update, or doing nothing wins surprisingly often, and the best Sch-M wait is the one you never queue.
Watching index operations with MonPG when SQL Server support lands
The signals that would have made my Saturday a non-event are Sch-M wait time as its own metric, the lock-queue depth behind any maintenance session, resumable operation state and percent complete, and maintenance-job runtime trended against the traffic curve so a rebuild overrunning its window pages before the checkout service does. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until then, a pre-flight query for open transactions on the target table, run at the top of the maintenance window, is the cheapest outage prevention I know.