SQL SERVER – Ins and Outs of Online Index Operations

As a SQL Server DBA, a few things worry me more than taking a table offline (tables are locked and hence like offline) to rebuild an index. The downtime required can significantly disrupt applications and users who need 24/7 access. That’s why online index operations are such a lifesaver! I recently talked about Ins and Outs of Online Index Operations with my clients during the Comprehensive Database Performance Health Check.

With online index builds and rebuilds, you can create and rebuild indexes without blocking access to the underlying table data. This allows end users and applications to continue querying and updating the table while the index operation occurs behind the scenes.

In this post, I’ll cover the basics of online index operations and some key points to be aware of when using them in SQL Server.

The ONLINE Option

You can specify the ONLINE option when creating or rebuilding an index to execute the operation online:

CREATE INDEX IX_Sales_Online ON Sales (SalesAmount) WITH (ONLINE = ON);

ALTER INDEX IX_Sales_Online ON Sales REBUILD WITH (ONLINE = ON);

This instructs SQL Server to acquire only a low-level shared lock on the table while the index is built or rebuilt. This allows concurrent SELECT and INSERT/UPDATE/DELETE statements to proceed on the table.

However, certain schema modification operations will be blocked while an online index operation runs. So you can’t drop, alter, or rebuild the table until the index build finishes.

Online Works for Rebuilding Too

The ONLINE option can be used whether you are creating a brand new index or rebuilding an existing one.

Rebuilding indexes online is great because it avoids downtime when you need to refresh an index after substantial data changes. The rebuilt index contains all the latest data from the table.

Some Key Caveats

While online index operations are fantastic, there are some limitations to be aware of:

• By default, they are multi-threaded, so use more resources. You can override this with MAXDOP = 1.
• They require extra temporary disk space during execution.
• Some index types like XML, spatial, or clustered column store indexes can’t be built online.
• Online operations on large tables with billions of rows may take longer than offline operations.
• An online operation may fail and revert to offline if specific data consistency issues arise during processing.
• It can cause increased transaction log usage during execution.

So keep these caveats in mind if you encounter any of these issues when building an index online.

Avoiding Blocks with Low-Priority Locks

Occasionally, an online index build may have to wait on another transaction’s schema modification (Sch-M) lock. By default, it will wait indefinitely, but you can specify low-priority lock behavior using the WAIT_AT_LOW_PRIORITY option:

ALTER INDEX...REBUILD WITH 
(ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION=30 MINUTES))

This makes the online rebuild wait in a low-priority mode, giving concurrent transactions priority access. You can also specify an optional MAX_DURATION, after which the online operation will abort.

The Solution: Resumable Online Indexes

The latest innovation that takes online index operations to the next level is resumable online indexes introduced in SQL Server 2019.

By adding the RESUMABLE = ON option, a long-running online index operation can be temporarily paused if needed, without failing. For example:

CREATE INDEX IX_Orders_Online ON Orders(OrderDate)
WITH (ONLINE = ON, RESUMABLE = ON)

The index build can then be resumed from where it left off using ALTER INDEX:

ALTER INDEX IX_Orders_Online ON Orders RESUME

This gives you the flexibility to schedule an extensive index rebuild during off-peak hours, pause it during the day if it conflicts with business-critical workloads, and resume at night. Amazing!

While resumable online indexes are a considerable advance, thoroughly test them before implementing them in production.

The Bottom Line

Online index operations unlock the ability to minimize and avoid downtime when managing indexes in SQL Server. Just be aware of the potential drawbacks, like increased resource usage. Features like resumable online indexes offer DBAs much finer control over managing the indexing process.

What about you? Have you used online index operations in SQL Server? Did you run into any gotchas? Share your experiences and thoughts below!

Reference: Pinal Dave (https://blog.sqlauthority.com)