With each release of SQL Server, there are usually new features or enhancements to existing features. One enhancement in SQL Server 2022 addresses the issue of resource contention during index creation or alteration. This new feature allows for the creation or alteration of indexes to wait for resources to become available before executing, reducing the impact on long-running queries with higher execution priority.

In previous versions of SQL Server, index creation on large tables could take several hours, causing delays and resource contention. The introduction of the “Wait at Low Priority” option in SQL Server 2022 provides a solution to this problem. This feature is also available in Azure SQL and Azure SQL Managed instances, but it is only supported in the Enterprise and Developer editions.

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

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

In the above syntax:

• 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 is required for this feature (only available in Enterprise or Developer editions)
• WAIT_AT_LOW_PRIORITY is the new setting
• MAX_DURATION specifies the maximum time in minutes to wait until an action
• ABORT_AFTER_WAIT determines whether to abort the operation if it exceeds the wait time

Let’s see an example of creating 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)
        )
     );

In the above example, the index creation will 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.

Alternatively, you can choose to kill sessions that are blocking after the specified wait time:

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

If everything is okay, the index will be created as expected.

This new feature also applies to altering indexes. If you need to modify the properties of an index or reorganize or rebuild an index, you can use the following syntax:

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

By utilizing the “Wait at Low Priority” option, SQL Server 2022 provides a more efficient way to handle index creation and alteration, reducing resource contention and improving overall performance. This feature is a valuable addition for database administrators and developers working with large tables and long-running queries.

Stay tuned for more updates and tips on SQL Server!

Article Last Updated: 2023-05-10