How to Optimize SQL Server Performance: Implementing Online DDL

Introduction:

Creating and Altering indexes for large tables can be a time-consuming process and may block other processes as well. However, with the introduction of the “Wait at Low Priority” 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. In this blog post, we will explore the syntax and usage of this option, along with its limitations and potential benefits.

Understanding the Syntax:

To use the “Wait at Low Priority” option during index creation, the following syntax can be used:

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

Here’s a breakdown of the different components:

• INDEX_NAME: Specifies the name of the index to be created.
• TABLENAME: Denotes the table on which the index will be created.
• COLUMNNAME: Represents the column(s) where the index will be applied.
• ONLINE = ON: This option must be enabled to utilize the “Wait at Low Priority” feature. - -WAIT_AT_LOW_PRIORITY: This setting signifies the new option we are interested in.
• MAX_DURATION: Allows you to specify the maximum waiting time in minutes for the operation
  to commence.
• ABORT_AFTER_WAIT: Determines the action to take if the wait time exceeds the specified
  duration. The options are:

1.  NONE: Waits for the lock with regular priority.
2.  SELF: Exits the online index operation.
3.  BLOCKERS: Kills transactions that are blocking the index rebuild.

Example:

To demonstrate how the “Wait at Low Priority” option can be used during index creation, consider the following example:

1. CREATE NONCLUSTERED INDEX [test_idx] ON [dbo].[tblAuthors]
2. (
3.     [Id],
4.     [Author_name] 
5. )WITH (ONLINE = ON 
6.         (WAIT_AT_LOW_PRIORITY 
7.            (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)
8.         )
9.      );
10. GO

In this scenario, we are creating a non-clustered index named “test_idx” on the “tblAuthors” table, specifically on the “Id” and “Author_name” columns. The index creation will wait for a maximum duration of 1 minute, and if it exceeds this time, the operation will be aborted or the index will be created if the resource is available.

Maintenance with ALTER INDEX:

The “Wait at Low Priority” option can also be utilized during index maintenance tasks like rebuilding or reorganizing. The below syntax can be used for index REBUILD & REORGANIZE:

ALTER INDEX index_name ON table_name REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = X MINUTES, ABORT_AFTER_WAIT = NONE/SELF/BLOCKERS) ) );

The components of this syntax are similar to the index creation syntax described earlier.

Limitations:

While the “Wait at Low Priority” option is a valuable feature, it does come with certain limitations.

The key points to consider are:

• The ONLINE argument must be set to ON for this option to be available.
• This functionality is only supported in the Enterprise and Developer editions of SQL Server 2022.

Conclusion:

The “Wait at Low Priority” option provides developers with a useful tool for managing index creation and maintenance operations. By leveraging this option, it becomes possible to prioritize these operations and make them more efficient.