3

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!

    2

    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.

    • Thanks for the help. Just to confirm, these are application related scripts that insert data into tables. We often run them when our applications are online. I was wondering if there was something designed out of the box for this purpose. Sounds like there is not. Thanks for replying. – xav Nov 11 '15 at 1:28

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.