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:
