By: Jeffrey Yao | Last Updated: 2014-07-16 | Comments (4) | Locking and Blocking
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?
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:
- Assign a "label" to the session for identification purposes
- 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:
- For a session considered low priority, at the beginning of the script insert
a line as
set context_info 0xdba911 -- arbitrary, and can be any value you like
- 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
- 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.
Last Updated: 2014-07-16
About the author
View all my tips