Online, Resumable, and WAIT_AT_LOW_PRIORITY operations in SQL Server

ONLINE operations in SQL Server were simple to understand for years – we got ONLINE index rebuilds in SQL Server 2005. That was it for a while.

Then, things got more complicated: we got more types of indexes. We got ONLINE options for schema changes that don’t involve indexes. We got more options for managing things like blocking, because online operations are really only mostly online — generally there’s going to be at least a short period where an exclusive lock is needed to update metadata. We now have some RESUMABLE operations coming in, too, for those big operations that are tough to handle.

Along the way, I fell behind. Because these features have steadily come out over a period of time, my brain simply didn’t register them all, or possibly I missed seeing them amid other announcements.

Having realized this, I did a little inventory: here’s my rundown of what I understand to be the current state of ONLINE options. Please feel free to add notes in the comments if I’ve missed things, and I’ll update the post accordingly. And I admit, in researching this post, I didn’t spend time looking at In-Memory OLTP tables … they come up so rarely that I didn’t go there.

ALTER TABLE ALTER COLUMN ONLINE (Disk-based rowstore)

Usage: most often changing nullability or data type. This is slow against large tables, because we’ve got to go rewrite all those rows and hold locks while we do it.

• ONLINE options in Azure SQL DB, SQL Server 2016 +
• Documentation lists the limitations and impacts (search for “as applies to altering a column” to find it in the page)
• Note: there are no WAIT_AT_LOW_PRIORITY or RESUMABLE options for ALTER COLUMN ONLINE

ALTER TABLE ADD COLUMN (Disk-based rowstore)

Usage: commonly done operation in routine software development when adding a new feature. This can be extremely slow when adding a non-nullable column with a default value, read about that in the link below.

• There is an Enterprise Edition feature for adding a non-nullable column with a default value in SQL Server 2012+
• Read about it from Remus Rusanu
• Note: this does not require or allow the use of ONLINE in the syntax. This is particularly tricky because it occurs in Developer Edition and doesn’t occur in Standard Edition 🤕

By the way, if you want to nerd out on what happens behind the scenes when you add or modify various types of columns, Remus has another excellent post on that topic.

CREATE INDEX ONLINE (Disk-based rowstore)

Usage: index creation is a common operation for both new features and performance tuning.

• ONLINE options in Azure SQL DB, SQL Server 2005+ (Enterprise Edition required)
• Documentation lists the limitations
• RESUMABLE added in SQL Server 2019
• Note: there is no WAIT_AT_LOW_PRIORITY option for CREATE INDEX

DROP INDEX ONLINE (Disk-based rowstore, clustered indexes only)

Usage: I am very hesitant to recommend this. I am mentioning it here to explain why it is probably not your friend and your attempt to do something ONLINE could end up in a world of regret with a full transaction log.

• ONLINE options in Azure SQL DB, SQL Server 2008+

• From the documentation: “When a clustered index is dropped OFFLINE, only the upper levels of clustered indexes are removed; therefore, the operation is quite fast. When a clustered index is dropped ONLINE, SQL Server rebuilds the heap two times, once for step 1 and once for step 2.”

• But wait, there’s more you should know: because nonclustered rowstore indexes use the clustering key as part of their own definition, this also causes rebuilds of all nonclustered indexes on the table – this can accidentally use a TON of space.

• If you need to drop a clustered index, generally this pattern is the best bet:

  • Drop or disable all nonclustered indexes (monitor for blocking, but a fast operation otherwise )

  • Drop the clustered OFFLINE (monitor for blocking, but a fast operation otherwise)

  • Create new clustered if desired (potentially with ONLINE option)

  • Create or rebuild all nonclustered indexes (potentially with ONLINE option)

If you find a situation where you want to use this, I recommend testing very carefully with a production-like data set before unleashing it on your database.

ALTER TABLE DROP CONSTRAINT ONLINE (Disk-based rowstore)

Similar to the DROP INDEX for clustered indexes, this works with indexes created by constraints. Since Clustered Primary Keys are implemented with constraints, this basically allows you to do the clustered index drop mentioned above for a clustered PK.

CREATE COLUMNSTORE INDEX ONLINE

Usage: columnstore index creation is increasingly common, as mixed workloads / reporting off of transactional databases is in high demand and far more viable from a software and hardware standpoint than it used to be.

• ONLINE option for Nonclustered Columnstore in Azure SQL Db, SQL Server 2017+
• ONLINE option for Clustered Columnstore in Azure SQL Db, SQL Server 2019+
• Documentation
• Note: there are no WAIT_AT_LOW_PRIORITY or RESUMABLE options for CREATE COLUMNSTORE INDEX

ALTER INDEX ONLINE (Disk-based rowstore)

Usage: commonly used to defragment indexes during maintenance operations, sometimes used to adjust fill factor and other index settings

• ONLINE options for rowstore in Azure SQL DB, SQL Server 2005+ (Enterprise Edition required for ONLINE rebuilds. Note that REORGANIZE is an online operation and doesn’t require Enterprise Edition, or the ONLINE keyword, however.)
• Documenation
• ALTER index is used for maintenance and for things like changing properties such as data compression, fill factor, and index padding
• WAIT_AT_LOW_PRIORITY in SQL Server 2014+ (using it requires ONLINE=ON)
• RESUMABLE = ON in SQL Server 2017_
• Single-partition ONLINE rebuild in SQL Server 2014+ (prior to that online rebuilds for partitioned indexes is only available for all partitions)

ALTER INDEX (columnstore indexes) - online by another name

Usage: maintenance operations (some details in the text below)

• Documentation
• “Starting with SQL Server 2016 (13.x), rebuilding a columnstore index is no longer necessary in most situations since REORGANIZE physically removes deleted rows and merges rowgroups. The COMPRESS_ALL_ROW_GROUPS option forces all OPEN or CLOSED delta rowgroups into the columnstore which previously could only be done with a rebuild. REORGANIZE is online and occurs in the background so queries can continue as the operation happens.”
• In other words, for most purposes for columnstore in 2016+, use REORGANIZE when you want REBUILD ONLINE 🙃

ALTER TABLE SWITCH PARTITION - ONLINE_ESQUE_

Usage: loading new data or removing data in “sliding window” scenarios

• WAIT_AT_LOW_PRIORITY in Azure SQL DB, SQL Server 2014+
• Documentation (search for “switch”)
• Note: this technically doesn’t have the ONLINE keyword, but the WAIT_AT_LOW_PRIORITY option makes it online-ish (or at least less offline, if that’s a thing)