SQL Server WAIT_AT_LOW_PRIORITY For CREATE or ALTER Index

Problem

With each release of SQL Server, there are usually new features or enhancements to existing features. An enhancement in SQL Server 2022 allows for creating or altering indexes to wait for resources prior to building or updating an index. This helps limit resource contention that may occur when maintaining indexes.

Solution

SQL Server 2022 added the “Wait at Low Priority” option that allows index creation to wait for resources to become available before executing. This feature reduces the impact on long-running queries that have a higher execution priority. When working with a big table, index creation can take several hours. That is why having some options to prioritize the operations is useful. This tip will explain the different options this new argument uses during index creation. This feature is also available in Azure SQL and Azure SQL Managed instances. Also, this option will only work with Enterprise and Developer editions.

Syntax

Let’s take a look at the syntax to understand how it works:

CREATE CLUSTERED INDEX INDEX_NAME
ON TABLENAME (COLUMNNAME)
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = X MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
        )
     );
GO

• INDEX_NAME – is the name of the index
• TABLENAME – is the name of the table where the index will be created
• COLUMNNAME – is the column or columns where the index will be applied
• ONLINE = ON – this needs to be used for this feature (NOTE: this is an Enterprise or Developer edition only feature)
• WAIT_AT_LOW_PRIORITY – is the new setting
  • MAX_DURATION – is used to specify the maximum time in minutes to wait until an action
  • ABORT_AFTER_WAIT – is used to abort the operation if it exceeds the wait time
    • NONE – is to wait for the lock with regular priority
    • SELF – exits the online index operation
    • BLOCKERS – kills transactions blocking the index rebuild

Create Test Table

First, let’s create a table named dbo.salesOrderDetailTest for testing from another table in the AdventureWorks database.

SELECT * INTO dbo.salesOrderDetailTest FROM [Sales].[SalesOrderDetail]

CREATE INDEX with WAIT_AT_LOW_PRIORITY

Add an index with the WAIT_AT_LOW_PRIORITY option.

CREATE CLUSTERED INDEX cindex 
ON [dbo].[salesOrderDetailTest] ([SalesOrderDetailID]) 
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = 50 MINUTES, ABORT_AFTER_WAIT = SELF)
        )
     );
GO

This will try to create the index and wait for 50 minutes if there is a process running with a higher priority. If it is still waiting after 50 minutes the create index operation will be cancelled.

The next example is similar, but it will kill sessions that are blocking after the 50 minutes and then create the index.

CREATE CLUSTERED INDEX cindex 
ON [dbo].[salesOrderDetailTest] ([SalesOrderDetailID]) 
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = 50 MINUTES, ABORT_AFTER_WAIT = KILL)
        )
     );
GO

If everything is OK, the index will be created as shown below.

ALTER INDEX with WAIT_AT_LOW_PRIORITY

If you need to modify the properties of an index or reorganize or rebuild an index, you can use ALTER INDEX as follows.

ALTER INDEX cindex 
ON [dbo].[salesOrderDetailTest]
REBUILD
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = 30 MINUTES, ABORT_AFTER_WAIT = SELF)
        )
     );
GO

Next Steps

To learn more about SQL Server indexes, please refer to these links:

• Rebuilding SQL Server indexes using the ONLINE option
• New and Enhanced Features in SQL Server 2022
• What is the Best Value for Fill Factor in SQL Server

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018