Friday , March 29 2024

How To Manage Online Index Rebuild and Partition Switch Processes With WAIT_AT_LOW_PRIORITY

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.

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

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.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories