Detect and Automatically Kill Low Priority Blocking Sessions in SQL Server

By: Jeffrey Yao   |   Updated: 2014-07-16   |   Comments (4)   |   Related: > Locking and Blocking

Problem

We have a customized database index maintenance job that runs every night, however from time to time the index maintenance job will block other business jobs or sessions and can cause business problems. So what should I do?

In a more generic scope, when a DBA needs to do some online maintenance work, such as creating a new index or doing some data cleaning, maintain a partition, etc., the work may cause some unexpected blocking to regular business sessions, so is there a reliable way to mitigate the blocking and reduce the potential impact to my business applications?

Solution

Blocking or deadlocking in SQL Server is unavoidable, but for deadlocking we can set the deadlock priority low for any sessions of less importance. However, there is no way to set blocking priority. So to ensure a session of less priority is not a long-time blocker in a blocking situation, we can do two things:

1. Assign a "label" to the session for identification purposes
2. Have a monitoring session to monitor sessions and if blocking exists check whether the blocker has that special "label", if so, we will kill the blocker session to resolve blocking.

From a technical perspective, the solution has two components:

1. For a session considered low priority, at the beginning of the script insert a line as follows:
    

   set context_info 0xdba911 -- arbitrary, and can be any value you like

2. Create a monitoring SQL Server Agent Job that is scheduled to run every X minutes (X can be 1 or any number suitable to your requirement).  The job checks whether a blocking issue exists, if so, whether the blocker has the label of low priority and if it does it will kill that process. The job's workload to check blocked processes is very small as it only queries two DMVs.
   

The monitoring script (applicable to SQL Server 2005 or later) is really simple.

-- find blocking sessions with special context_info and kill the sessions
set nocount on;
set deadlock_priority low;
declare @sqlcmd varchar(max);
declare @debug bit; -- 1=print out kill command, 0=execute kill command

set @debug = 1; -- 1=print, 0=exec
set @sqlcmd='';

; with cte (Session_id, Context_info) as
(
select r1.session_id, r1.context_info from sys.dm_exec_requests r1 with (nolock)
inner join sys.dm_exec_requests r2 with (nolock)
on r1.session_id = r2.blocking_session_id 
where r1.session_id > 50
and r1.session_id <> @@spid
union 
select s.session_id, s.context_info from sys.dm_exec_sessions s with (nolock)
inner join sys.dm_exec_requests r with (nolock)
on s.session_id = r.blocking_session_id
and r.session_id <> @@spid
)
select @sqlcmd = @sqlcmd + 'kill ' + cast(session_id as varchar) +';' + char(0x0d) + char(0x0a) from cte
where context_info = 0xdba911; -- 0xdba911 for labelling low priority sessions
if @debug = 1
 print @sqlcmd;
else
 exec (@sqlcmd);

(Fig-1 Blocking Discovery Code)

Demo to Test Killing Low Priority Blocking Processes in SQL Server

In SSMS, we open two query windows.  In the first window we will run the following code, which we assume is of low priority, i.e. during the blocking scenario, this session can be killed if it blocks any other business sessions considered high priority.

use AdventureWorks2012
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;
create table dbo.t (a int, b varchar(30));
go
insert into dbo.t (a, b) values (1, 'hello')
go
set context_info 0xdba911; -- change 0xdba911 to your preferred value
begin tran 
update dbo.t set b= 'world' from dbo.t where a=1
waitfor delay '00:05:00' -- wait for 5 min
commit tran;

In the second window, we will run the following code assumed to be of high priority. This session will be blocked by the session in the first window

use AdventureWorks2012
delete from dbo.t where a=1; 

In my test environment, the first SSMS query window has spid=57 and the second has spid=58.

Now, open a third SSMS query window and run the code from Fig-1.  If I set @debug=1 this will just print the kill command and not actually kill the session.  I expect to see a message to kill spid 57 to allow spid 58 to continue without being blocked.  So here is what I get:

So the result is exactly as expected, i.e. kill 57

Next Steps

• To deploy this tip, create a SQL Server Agent Job that embeds Fig-1 code in a job step, make sure to set @debug = 0 and schedule the job to run every X minutes.
• For any low priority jobs (such as nightly index maintenance or table maintenance), add a line: "set context_info 0xdba911" in the beginning of the script
• You may consider creating a central monitoring job to monitor all other servers to ensure that DBA related sessions are not blocking other business sessions for longer than X minutes (i.e. the frequency of the monitoring job).

Note: In SQL Server 2014, when doing index maintenance, there is a new clause WAIT_AT_LOW_PRIORITY, which can "kill" itself if it blocks others for a specified duration. I guess Microsoft has realized the deficiency of the previous index maintenance work and thus made this improvement.

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips