Deploy at Low Priority

Learn how to deploy schema changes in an always-online SQL Server environment without a maintenance window. This utility reduces blocking and improves deployment success.

The problem 🔗︎

I attempted to deploy a trigger change on a load-bearing table in a busy system. I was repeatedly blocked and, while waiting for the SCH-S lock, I ended up blocking other queries. I group blockers into two categories based on duration:

• Long
  • You have enough time to detect the block and query WhoIsActive or another DMV utility to identify the blocker.
  • Can you kill it? It might be critical for business or have a long rollback time.
• Short
  • These blockers come and go so quickly you might not even notice them.
  • You’re usually blocked due to lock partitioning and your session running on a scheduler with a higher ID. (as I cover in Async stats update causing blocking)

Ideally, deployments should happen when there’s no active locking on the object. This reminded me of WAIT_AT_LOW_PRIORITY feature of the online index rebuilds.

It would be great if this option were available for all schema changes, but since it isn’t, I decided to build my own version.

Building blocks 🔗︎

To build this utility, we’ll need:

• A time-based loop (infinite loops can go wrong)
• Check for incompatible locks
• The actual code to be deployed.
  • Most of the code deployments are required to be in a separate batch (in SSMS the batch separator is GO by default)
• End gracefully if you cannot get the lock in the defined time
• Any other safety measures

The main challenge was how to put the deployment script in a separate batch. The first method that came to mind was using dynamic sql but that would mean using dynamic sql (and all its pitfalls like double apostrophes, formatting issues, lack of syntax highlighting, etc). That’s why I chose the rarely used GOTO.

The script 🔗︎

set deadlock_priority low;
set xact_abort on;
set lock_timeout 2000; /* milliseconds */

set noexec off /* resets the noexec on that's set below */
declare @startTime datetime2(0) = sysdatetime()
declare @max_duration int = 5 /* minutes */
declare @objectId bigint = (select object_id('')) /* <-- insert the full schema.objectname */
declare @dbId int = (select db_id()) /* Assuming the same DB context, but feel free to modify */

while dateadd(minute, @max_duration, @startTime) > sysdatetime()
begin

    if not exists (  /* Check if an incompatible lock exists */
      select top 1
        1
      from sys.dm_tran_locks as dtl
      where
          dtl.resource_database_id = @dbId
          and dtl.resource_associated_entity_id = @objectId
          and dtl.request_status = N'GRANT'
          and dtl.request_mode = 'SCH-S'
    )
    begin
        goto gotta_go_fast
    end

    WAITFOR DELAY '00:00:00.200' /* Format: hh:mm:ss.mss */
end

/* after the timeloop finishes without success, prevent execution of further code*/
    set noexec on

gotta_go_fast:

/*
###############################################################################
  Insert the deploy code in the batch block below (bordered by GO separators) 
###############################################################################
*/
go

go
/*
###############################################################################
  The end of the deploy batch block
###############################################################################
*/

Explanation 🔗︎

WARNING:

⚠️ Test lock-checking performance before running it in a loop. Also, ensure the actual execution plan is disabled.

• I start with some safety measures in a form of deadlock priority and lock timeouts
• Variable values are in the subquery format so you can highlight and check the values before running
• Inside the time loop I have the SCH-S lock check
  • The top 1 1 is not necessary for the exists but in case you want to test the subquery with values instead of variables
• If no lock is detected, jump to the gotta_go_fast: label which is right before the batch separator go
  • You can test around which waitfor delay works best for you. The goal is to limit the number of loops so it’s not too resource intensive
• If you cannot find a window of opportunity within the @max_duration minutes we’ll set the noexec on to prevent any further code from running
  • the noexec off is at the start of the code to reset to the original state
• Do not forget the trailing go to properly terminate the batch

A more aggressive version of the script 🔗︎

If some blocking is acceptable, replace the no-lock check with a lock threshold check and change the lock timeout to something longer.

set lock_timeout 8000;
…
if (  /* test for a lock count */
      select count(1)
      from sys.dm_tran_locks as dtl
      where
          dtl.resource_database_id = @dbId
          and dtl.resource_associated_entity_id = @objectId
          and dtl.request_status = N'GRANT'
          and dtl.request_mode = 'SCH-S'
    ) <= 5
…

As always, thanks for reading. Hope you find this useful!