Safely Running Long-Running Index Operations in SQL Server

Creating or rebuilding an index on a large, actively used table is not merely a schema change. It is a production operation that can consume significant CPU, I/O, transaction log, and storage resources, and it can introduce unexpected blocking even when the operation uses ONLINE = ON.

This article explains a particularly subtle failure mode: an online index operation holds a schema-stability lock while an automatic statistics update waits for a schema-modification lock. SQL Server's lock queue can then place otherwise compatible application queries behind the waiting schema-modification request, creating a lock convoy and widespread query timeouts.

This is one possible blocking pattern, not the only cause of blocking during an online index operation.

The broader lesson is simple:

Treat long-running index changes as controlled operational events. Estimate them, schedule them, monitor them, make them interruptible where possible, and define the recovery path before execution.

Why an Online Index Operation Can Still Block Queries

ONLINE = ON reduces blocking, but it does not mean "no locks." An online index operation still acquires locks during its lifecycle, including:

• A long-lived schema-stability (Sch-S) lock during much of the build.
• A short table-level shared (S) lock during preparation and a short schema-modification (Sch-M) lock during the final metadata switch.
• Intent locks on the source and target structures while the new index is built and maintained.

Sch-S locks are compatible with other Sch-S locks. Under normal conditions, queries can compile and execute while the index is being built.

The problem begins when another operation requests an incompatible Sch-M lock.

The initial S lock can also wait behind an open transaction that modified the table. In that case, the waiting index operation can delay new writers before the build has fully started.

The Lock-Convoy Failure Mode

Consider this sequence:

Online index operation Holds: Sch-S on the target table | +-- blocks --> Statistics update Wants: Sch-M | +-- queues ahead of --> New statements Want: Sch-S 

The new queries need Sch-S, which is compatible with the index operation's existing Sch-S. However, a waiting Sch-M request is already in the lock queue.

A lock request is generally not granted ahead of an earlier incompatible waiter, even when it is compatible with the locks currently granted. This behavior prevents an exclusive waiter from starving indefinitely, but it can create a convoy.

The result is a convoy:

1. The index operation holds Sch-S for an extended period.
2. A statistics update requests Sch-M and must wait.
3. New statements touching the table request Sch-S.
4. Those queries queue behind the pending Sch-M.
5. Application requests accumulate and eventually reach their command timeout.

This behavior can make a database appear intermittently unavailable even though the index operation is running with ONLINE = ON.

Why Automatic Statistics Updates Can Trigger the Convoy

On an active table, inserts, updates, and deletes increase statistics modification counters. During a long index operation, one or more statistics objects may cross the automatic-update threshold.

When a compilation detects stale statistics, SQL Server may attempt to update them. In some engine versions and execution paths, the update can request Sch-M. If an online index operation is still holding Sch-S, the statistics update waits and can become the head waiter in the blocking queue.

This explains why problems may not begin immediately:

1. The index operation starts and queries initially continue normally.
2. Data modifications accumulate.
3. A statistics object becomes stale.
4. A query compilation triggers the statistics update.
5. The waiting Sch-M request creates the convoy.

The delay between the start of the index operation and the first visible query failures can make the relationship difficult to identify. Confirm the mechanism rather than assuming it: look for an LCK_M_SCH_M waiter associated with statistics activity, blocked by the index session, followed by Sch-S waiters.

Why Recompilation Amplifies the Impact

Queries acquire schema-stability locks on referenced objects while compiling and executing. A queued Sch-M request can therefore delay all new statements touching the table, including statements that reuse cached plans.

Frequent recompilation can still amplify the impact because it adds compilation work and more lock requests during an already congested period.

Common causes include:

• OPTION (RECOMPILE)
• Stored procedures declared WITH RECOMPILE
• Plan-cache eviction
• Frequent ad hoc queries
• Schema changes that invalidate plans
• Statistics changes that trigger recompilation

A cached plan avoids repeated optimization work, but it does not allow execution to bypass the schema-lock queue. A query using OPTION (RECOMPILE) adds compilation on every execution, increasing pressure and the number of waiting requests during a convoy.

Recompilation is not inherently wrong, but it must be considered when planning maintenance against hot tables.

Why the Symptoms May Be Intermittent

Temporary relief can occur when the waiting Sch-M request is cancelled by a client timeout, a configured lock timeout, or an explicit operator action. Removing that request allows the queue to drain.

When that happens:

1. The queued Sch-S requests can be granted.
2. A burst of queries completes.
3. A later compilation detects stale statistics again.
4. Another statistics update requests Sch-M.
5. The convoy returns.

This produces a repeating pattern:

Blocking -> temporary relief -> blocking -> temporary relief 

Intermittent success does not prove that the index operation is healthy. It may indicate that the head waiter is repeatedly cancelled and requested again.

Risk Factors

The likelihood and impact of this pattern increase when several of the following are true:

Plan the Change Before Running It

1. Measure the Target

Collect at least:

• Table and index size
• Row count
• Current write rate
• Peak and off-peak workload
• Available database and temporary storage
• Existing index definition
• Edition and engine-version support for online and resumable operations
• Temporary-database capacity when using options that require it
• Planned MAXDOP and its effect on duration and resource use
• Recovery model and transaction-log backup throughput
• Replication, availability, or disaster-recovery constraints

Do not estimate duration solely from a development or staging database. Index build time does not scale linearly across different hardware, data distributions, workload levels, and log configurations.

2. Establish Success and Abort Thresholds

Define measurable thresholds before execution, such as:

• Maximum acceptable blocked-query count
• Maximum lock-wait duration
• Maximum application error or timeout rate
• CPU, I/O, and log-utilization limits
• Maximum operation duration
• Minimum free-storage reserve

3. Choose the Execution Model

Use the simplest model that provides enough safety:

Long-running data and index operations should generally be separated from fast schema changes. A routine application rollout should not depend on a multi-hour physical data operation finishing in the same execution window.

4. Test with Production-Like Conditions

A useful rehearsal should include:

• Representative data volume and distribution
• Concurrent reads and writes
• Queries that commonly recompile
• Monitoring of lock waits and blocking chains
• An intentional pause or abort
• Validation of retry or resume behavior

The goal is not only to prove that the index can be created. It is to prove that the system remains operable while it is being created.

Execution Safeguards

Run During the Lowest Practical Traffic Window

Lower traffic reduces:

• Foreground workload contention
• Data changes that must be reflected in the new index
• Transaction-log generation
• The probability of statistics becoming stale during the operation
• The number of queries affected by an unexpected blocking chain

An online operation should still be scheduled thoughtfully. "Online" is a concurrency feature, not a maintenance-window substitute.

Use Low-Priority Lock Waiting

For supported operations, use WAIT_AT_LOW_PRIORITY so the index operation's own lock requests do not indefinitely block normal workload. Test all syntax against the deployed engine version and edition; support differs between CREATE INDEX and ALTER INDEX ... REBUILD.

ALTER INDEX [IX_Example] ON [dbo].[LargeTable] REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF ) ) ); 

ABORT_AFTER_WAIT = SELF favors workload availability by cancelling the index operation if it cannot obtain the required lock within the specified period. For a non-resumable operation, cancellation discards completed build work and rollback can take time. ABORT_AFTER_WAIT = BLOCKERS can terminate user sessions and should be used only with explicit operational approval.

Choose the duration based on the workload's latency objectives. Do not copy a large value without understanding how long the operation may remain pending.

This option reduces blocking caused by the index operation's own lock requests. It does not prevent a separate statistics-update Sch-M request from queueing while the build holds Sch-S.

Consider Resumable Index Operations

Where supported, resumable operations reduce the cost of interruption:

CREATE INDEX [IX_Example] ON [dbo].[LargeTable] ([KeyColumn]) WITH ( ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 120 MINUTES ); 

If the operation reaches MAX_DURATION, it pauses rather than discarding all progress. It can later be resumed:

ALTER INDEX [IX_Example] ON [dbo].[LargeTable] RESUME WITH (MAX_DURATION = 120 MINUTES); 

Inspect resumable operations with:

SELECT object_id, index_id, name, state_desc, percent_complete, page_count FROM sys.index_resumable_operations; 

Resumability introduces operational responsibilities:

• A paused operation consumes storage.
• Writes may need to maintain both index structures.
• Operations requiring a table-level exclusive lock, such as TRUNCATE TABLE, some ALTER TABLE statements, and offline index rebuilds, cannot proceed while the index is paused.
• Automation must detect and resume, complete, or abort abandoned operations.

Resumability does not prevent the lock-convoy pattern while the operation is actively running. Enable it only when someone owns monitoring and cleanup for paused work.

Control Automatic Statistics Updates Deliberately

For a verified lock-convoy risk, temporarily disabling automatic statistics updates can prevent a statistics update from requesting Sch-M during the vulnerable period. Treat this as a last-resort mitigation after scheduling, low-priority waiting, resumability, and active monitoring have been considered.

Prefer the narrowest effective scope. Inspect the current table-level settings before changing them:

EXEC sys.sp_autostats N'dbo.LargeTable'; 

If testing confirms that table-scoped suppression addresses the risk, record the existing state, disable updates for the target table, and restore the recorded state afterward:

EXEC sys.sp_autostats N'dbo.LargeTable', N'OFF'; -- Run the controlled index operation here. -- Restore each statistic to its previously recorded setting. 

If a database-level change is required, first record is_auto_update_stats_on from sys.databases and restore that original value rather than hard-coding ON. A database-level change affects every table; stale statistics can accumulate and query quality can degrade. Switching to asynchronous automatic statistics does not eliminate this risk because the statistics update can still request the incompatible lock.

Use this mitigation only when:

• The lock-convoy mechanism has been validated for the workload.
• The maintenance window is bounded.
• The setting change is monitored.
• Re-enabling is part of the mandatory cleanup path.
• Critical statistics are updated manually afterward when appropriate.

After the index operation has finished, update other affected statistics when appropriate. The newly created index already receives index statistics during creation.

UPDATE STATISTICS [dbo].[LargeTable]; 

The appropriate sampling level depends on table size, workload, and maintenance budget. A full scan can itself be expensive.

Ensure cleanup runs even after cancellation, timeout, or failure, and alert if any temporary statistics configuration remains in place.

Avoid Unnecessary Recompilation During the Window

If application behavior permits, reduce avoidable compilations during maintenance:

• Reassess unconditional OPTION (RECOMPILE) usage.
• Avoid unnecessary plan-cache clearing.
• Do not combine unrelated schema changes with the index operation.

Do not remove recompilation hints blindly. They may exist to solve parameter-sensitivity or plan-quality problems. Any query change requires independent performance validation.

Monitoring During the Operation

Find Active Requests and Blockers

SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time, r.wait_resource, r.blocking_session_id, r.percent_complete, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests AS r INNER JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id WHERE r.database_id = DB_ID() AND s.is_user_process = 1 AND r.session_id <> @@SPID ORDER BY r.total_elapsed_time DESC; 

percent_complete is populated only for some commands and may remain zero for an index operation.

Look specifically for:

• LCK_M_SCH_M
• LCK_M_SCH_S
• A growing number of blocked sessions
• A head blocker associated with statistics or index maintenance
• Increasing application timeout rates

Inspect Locks on the Target Object

SELECT tl.request_session_id, tl.resource_type, tl.request_mode, tl.request_status, tl.resource_associated_entity_id FROM sys.dm_tran_locks AS tl WHERE tl.resource_database_id = DB_ID() AND tl.resource_type = N'OBJECT' AND tl.resource_associated_entity_id = OBJECT_ID(N'dbo.LargeTable') ORDER BY tl.resource_associated_entity_id, tl.request_session_id; 

This DMV does not expose queue order. Correlate it with sys.dm_exec_requests, sys.dm_os_waiting_tasks, and SQL text when deeper investigation is required. Capture evidence before terminating an operation, provided doing so does not delay recovery.

Monitor Resource Health

At minimum, track:

• CPU
• Data-file and log-file I/O latency
• Transaction-log usage and growth
• Database and temporary-storage free space
• Query latency and timeout rate
• Deadlocks
• Blocking duration
• Index-operation progress

Database health and application health must be observed together. A database command can be progressing normally while the application is already outside its service objectives.

Abort and Recovery

Stopping a non-resumable index operation can require a lengthy rollback. The database may remain under resource pressure while that rollback proceeds.

If a verified statistics-related LCK_M_SCH_M waiter is causing the convoy, cancelling that waiting session may drain the queue while preserving the index build. Confirm the session's identity and SQL text first; cancelling it also cancels the triggering query.

Before execution, document:

1. How to identify the index session.
2. The exact command used to stop or pause it.
3. Whether progress will be preserved.
4. How rollback progress will be monitored.
5. Which temporary database settings must be restored.
6. How statistics and index state will be validated afterward.
7. When the operation may be retried.

For a resumable operation, prefer PAUSE when the goal is to preserve progress:

ALTER INDEX [IX_Example] ON [dbo].[LargeTable] PAUSE; 

If the partial operation must be discarded:

ALTER INDEX [IX_Example] ON [dbo].[LargeTable] ABORT; 

ABORT discards saved progress and may take time. Confirm the supported syntax and behavior for the deployed engine version before the maintenance window.

Post-Change Validation

Do not declare success when the DDL statement returns. Verify:

• The intended index exists and is enabled.
• The index definition matches the approved design.
• No resumable operation remains paused unexpectedly.
• Temporary database settings have been restored.
• Statistics are sufficiently current.
• Blocking and timeout rates have returned to baseline.
• Query plans use the new index where expected.
• Write latency has not regressed unexpectedly.
• Data and log files have adequate free space.
• The maintenance record reflects the final state.

Example checks:

SELECT i.name, i.type_desc, i.is_disabled, i.is_hypothetical, i.has_filter, i.filter_definition FROM sys.indexes AS i WHERE i.object_id = OBJECT_ID(N'dbo.LargeTable'); 

SELECT s.name AS statistic_name, sp.last_updated, sp.rows, sp.modification_counter FROM sys.stats AS s OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp WHERE s.object_id = OBJECT_ID(N'dbo.LargeTable') ORDER BY sp.last_updated; 

Recommended Operational Checklist

Before

• [ ] Measure table, index, log, and storage requirements.
• [ ] Estimate duration using representative data and workload.
• [ ] Confirm online and resumable feature support.
• [ ] Identify queries that frequently compile or recompile.
• [ ] Select a low-traffic window.
• [ ] Define success, pause, and abort thresholds.
• [ ] Prepare monitoring queries and dashboards.
• [ ] Test pause, resume, abort, and cleanup behavior.

During

• [ ] Monitor application latency and error rate.
• [ ] Monitor Sch-S and Sch-M waits.
• [ ] Watch the blocking chain, not only the index session.
• [ ] Track CPU, I/O, storage, and transaction-log health.
• [ ] Pause or abort when a predefined threshold is crossed.

After

• [ ] Confirm the final index definition and state.
• [ ] Restore temporary database settings.
• [ ] Enable statistics auto update as needed.
• [ ] Confirm no resumable operation remains paused.
• [ ] Validate critical query performance.
• [ ] Record actual duration, resource usage, and lessons learned.

Final Takeaways

1. Online does not mean lock-free. Online index operations still interact with schema locks and can affect production traffic.
2. A waiting Sch-M request can create a lock convoy. Later Sch-S requests may queue behind it even though they are compatible with the lock currently being held.
3. Automatic statistics can create a head waiter, and recompilation can add pressure. All new statements need schema stability; frequent recompilation adds more work and requests during the convoy.
4. Large index changes need an operational design. Maintenance windows, monitoring, abort criteria, and cleanup are part of the implementation.
5. Resumable operations reduce interruption cost but require lifecycle management. Paused work must be visible, owned, and completed or aborted.
6. Temporary configuration changes must be bounded and reversible. If automatic statistics are disabled, restoring them is a mandatory recovery action.
7. Separate slow maintenance from routine application rollout when scale demands it. Fast schema changes and multi-hour physical data operations have different operational requirements.

The safest index change is not the one with the most options in its WITH clause. It is the one whose production behavior has been estimated, rehearsed, observed, and made recoverable.