Sql-server – Lock priority for scripts run in maintenance windows

blockingsql server

We run maintenance windows weekly and monthly. Some of our maintenance windows are online and the last thing we want is our scripts to block live queries from our applications.

Is there a way to set the priority of a query so that it cannot cause other queries to block for more than X seconds?

Example:

  • I run maintenance script A on tableA
  • Application is updating a row on tableA, waits on the maintenance script to complete.
  • SQL Server automatically terminates and rollback my maintenance script after X seconds of blocking.

    I know there is a similar functionality with deadlocks (set deadlock victim to low).

    Thanks!

    • Best Answer

    Microsoft introduced the new Low Priority Lock Wait option for Index rebuilds in SQL Server 2014. Not sure what SQL Server version you are running.

    Index rebuild using 'low priority lock wait':

    ALTER INDEX Index_name
ON Schema.Table_name
REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY 
             (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )))
;

    Check out Paul Randal's excellent article here Low Priority Wait and Testing Index rebuilds with Low Priority Wait by Kendra Little for clear explanation.

    That's specific for Index maintenance, I am assuming your maintenance plans does include Integrity checks and other stuff too.

    An option would be writing some TSQL code to monitor blocking and and create and schedule a SQL agent job to automatically kill the 'low priority session' based on the SQL text its running.

    If you are experiencing issues caused by deadlocks, you could probably lower the Deadlock priority value to see if that helps.

    Depending on the type of resource contention, you can also try limiting the resources for specific logins using Resource Governor.

    If you are using custom maintenance plans, you probably need to look into and start using Ola Hallengren's Maintenance solution.

    Related Question