Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
WAIT_AT_LOW_PRIORITY is a feature introduced with SQL Server 2014. We can control the Online Index Rebuild and switch partition operations.
For example, suppose we perform Online Index Rebuild operation. The Online Index Rebuild transaction can be locked by a transaction. And the process may not be completed for a long time.
For example, an application developer started a transaction and did not commit. The Online Index Rebuild operation can wait for lock until this commit completed. With WAIT_AT_LOW_PRIORITY, we can prevent this.
Let’s examine the following query.
After WAIT_AT_LOW_PRIORITY;
“MAX_DURATION = 1 MINUTES” means wait 1 minute if the online index rebuild remains locked.
“ABORT_AFTER_WAIT = SELF” means cancel the online index rebuild after waiting 1 minute.
If we wrote NONE instead of SELF, the online rebuild index would continue.
If we wrote BLOCKERS, the query that locked the online index rebuild process would be killed.
1 2 3 4 5 | ALTER INDEX IndexName ON dbo.TableName REBUILD WITH (ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF )) ); |
You can also use it for the switch partition operation as follows. You may want to read the article “Sliding Window-Switch Partition-Split Range-Merge Range”
1 2 3 | ALTER TABLE [DBName].[dbo].[TableName] SWITCH PARTITION 12 TO [DBName].[dbo].[TableNameArchive] WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)); |
You may also want to read the article “How To Check Online Index Rebuild Progress Using Extended Events” to find out when the Online Index Rebuild process will finish.
