Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on different client computers. Some of the actions needing locking protection may not be obvious, for example, locks on system catalog tables and indexes. Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes, all waiting on other processes for locks. There are many blocking scenarios such as submitting queries with long execution times, applications that are not processing all results to completion, etc. The application code itself must be designed appropriately and monitored for efficiency. In fact, most experts agree that as much as 70 to 80 percent of performance and blocking problems are caused by improperly coded database applications. SQL is the primary culprit. And this is why one of the common tasks in multi-user multi-database environment is to monitor the processes that are blocked by another process for more than X number of seconds.
Most of the time, blocks don't cause problems unless it is long time blocking. Locking problems arise only when a process holds a lock longer than necessary and blocks other users' processes
The following categories show the division of application blocking processing based on our DBA department decision:
- Short-time Blocking - average duration less than three seconds. They do not create problems.
- Mid-time Blocking - average duration ranges from 3 seconds to a maximum of 10 seconds. Usually do not create problems
- Long-time Blocking - average duration more than 10 seconds. Require attention.
Deadlocks - SQL Server automatically terminates one of the blocked processes.
It is doesn’t mean that division for every company or scenario may not vary. But our application’s connections timeout is 15 seconds and with long-time blocking situation some application’s connections start timeout.
In the environment, where 200+ databases and new databases created on the weekly/daily bases, the monitoring task is required partial or complete automation and a notification when blocking is occur. The purpose of this article is not discussing a strategy for the resolution of SQL Server blocking problems but to show how to automate the task of monitoring and notification if blocking situation happen and lasts for more that X number of seconds. It is understandable, that this task should be scheduled.
As always, I am not pretending to find the perfect or the best solution. The point many of my articles is to present a task, describe the ideas of how to solve the task and show one or few various (may not be the best but) way(s) of solution implementation. For sake of clarity, all stored procedures will not include an error handler, most parts for the variables verification, and some other advanced features.
Sysprocesses is the system table that can reveal the most clues about why a process is blocking others. The sysprocesses table holds information about processes running on Microsoft® SQL Server™. These processes can be client processes or system processes. Sysprocesses is stored only in the master database. Let’s see the source code of developed procedure.
Developed stored procedure has 3 parameters:
@blocker_threshold shows how many blocking processes must exists before send an email,
@email_list is the list of the recipients to whom send an email, @wait_time_threshold define a threshold for the blocking.
create proc dbo.P_blockerS @blocker_threshold smallint = 1, @email_list varchar(255) = 'sqldbagroup' , @wait_time_threshold int as -- This procedure will assist in identifying SQL Blockers -- If blocking exceeds a threshold supplied as a parameter it executes xp_sendmail BEGIN set nocount on Declare @total_blockers smallint, @waittime int , @maxid int, @minid int declare @blocker_spid smallint, @blocker_spid_info varchar(500) , @blocker_spid_msg varchar(7000) declare @tbl_blockers table (blocker_spid int,tid int identity(1,1)) select @total_blockers = count(*) from master..sysprocesses where blocked > 0 and waittime > @wait_time_threshold set @blocker_spid = 0 set @blocker_spid_info = '' set @blocker_spid_msg = CAST(@total_blockers as char(3)) + process(es) are blocked. Blocking info: ' IF (@total_blockers > 0 ) -- if there are blockers BEGIN insert into @tbl_blockers (blocker_spid) select spid from master..sysprocesses where blocked > 0 and waittime > @wait_time_threshold union select blocked from master..sysprocesses where blocked > 0 and waittime > @wait_time_threshold select @minid = min(tid), @maxid = max(tid) from @tbl_blockers WHILE ( @minid <= @maxid ) BEGIN -- begin while loop if ( @total_blockers >= @blocker_threshold ) BEGIN select @blocker_spid = blocker_spid from @tbl_blockers where tid = @minid select @blocker_spid_info = 'Block on ' + rtrim(@@servername) + ', SPID='+ rtrim(convert(varchar(6),p.spid))+', bloked by '+ isNull(cast(blocked as varchar(9)), ' ') + ', '+ rtrim(convert(varchar(15),IsNull(p.status, ' ')))+' , '+ rtrim(convert(varchar(25),IsNull(p.loginame, ' ')))+' , '+ rtrim(convert(varchar(15),IsNull(p.hostname, ' ')))+' , '+ rtrim(convert(varchar(30),IsNull(p.program_name, ' ')))+' , '+ rtrim(convert(varchar(25),IsNull(p.cmd, ' ')))+', login_time='+ rtrim(convert(varchar(19),IsNull(p.login_time,'1900-01-01'),121))+', last_batch='+ rtrim(convert(varchar(19),IsNull(p.last_batch,'1900-01-01'),121)) + ' ' from master..sysprocesses p where p.spid = @blocker_spid end set @blocker_spid_msg = @blocker_spid_msg + @blocker_spid_info set @minid = @minid + 1 END -- end while loop exec master..xp_sendmail @recipients = @email_list , @subject = 'Blocking Process' , @message = @blocker_spid_msg END -- if blockers END
Another developed procedure p_blocking_notify gives an extended example for the process of monitoring blocking connections. This procedure assists in identifying SQL Blockers. And, in the case of existence of a blocking process, procedure will not only send an email but provide an ability to kill the blocker connection based on application name and number of seconds’ another process is blocked. The choice of killing (disconnecting) the blocking connection is controlled by the parameter @kill_flag. If the value is ‘Y’ blocking connection will be terminated. If blocking exceeds a threshold supplied as a parameters @blocker_threshold and @waittime_threshold then it executes an raiserror and xp_sendmail.
Usually I am creating a job that runs ones in 1-2 minutes or based on blocking prediction time the schedule may vary (for example, every 2-5 minutes). In addition, stored procedure can be modified to insert results into a history table that may give the ability for the better analysis over the time. I am utilizing this procedure when I need to analyze an intermediate blocking process that may happen rarely. In this case SQL Profiler is useless because excessive blocking situation may not appear within days or even weeks.
create proc dbo.p_blocking_notify @blocker_threshold smallint = 1, @email_list varchar(255) = 'email@example.com' , @program_name_par1 varchar(255), @waittime_threshold int, @num_of_blocked_processes int, @kill_flag char(1) = 'N' as BEGIN set nocount on Declare @total_blockers smallint, @cnt int, @waittime int , @program_name varchar(50), @APP_total_blockers int declare @blocker_spid smallint, @blocker_spid_info varchar(250) , @below_thres_msg varchar(30) declare @blocker_spid_msg varchar(5000), @blocker_spid_msg1 varchar(250) , @above_thres_msg varchar(255) select @total_blockers = count(*) from master..sysprocesses where blocked > 0 select @APP_total_blockers = count(*), @waittime = max(waittime) from master..sysprocesses where blocked > 0 and program_name = @program_name_par1 set @cnt = 1 WHILE ( @APP_total_blockers > 0 and @waittime >= @waittime_threshold and @cnt < @num_of_blocked_processes) BEGIN -- begin while loop set @cnt = @cnt + 1 -- The following statements can be modified to insert results into a history -- table. If run from SQL Executive a blocker count will appear in history if (@total_blockers < @blocker_threshold) begin set @below_thres_msg = 'Total Blockers at '+convert(char(3),@total_blockers) print ' ' print @below_thres_msg end else BEGIN set @above_thres_msg = ' Blocking Threshold Exceeded, Threshold at ' + convert(char(3),@blocker_threshold) + ' Total Blockers at ' + convert(char(3),@total_blockers) print ' ' print @above_thres_msg end -- Send a Message to the log. Can utilize message for SQL Alert RAISERROR (@above_thres_msg,16,1) with log set rowcount 1 select @blocker_spid = (select TOP 1 spid from master..sysprocesses where blocked = 0 and spid in ( select blocked from master..sysprocesses where blocked > 0 ) ) select @total_blockers 'Total Blocked Processes' select @blocker_spid 'This is the Culprit' select @blocker_spid_info = (select 'Block on ' + rtrim(@@servername) + ', SPID=' + rtrim(convert(char(6),p.spid))+', '+ rtrim(convert(char(15),IsNull(p.status, ' ')))+' ,'+ rtrim(convert(char(25),IsNull(p.loginame, ' ')))+' ,'+ rtrim(convert(char(15),IsNull(p.hostname, ' ')))+' ,'+ rtrim(convert(char(30),IsNull(p.program_name, ' ')))+' ,'+ rtrim(convert(char(25),IsNull(p.cmd, ' ')))+', login_time='+ rtrim(convert(char(19),IsNull(p.login_time,'1900-01-01'),121))+',last_batch='+ rtrim(convert(char(19),IsNull(p.last_batch,'1900-01-01'),121)) from master..sysprocesses p where p.spid = @blocker_spid) select @program_name = RTrim(Ltrim(p.program_name)) from master..sysprocesses p where p.spid = @blocker_spid set rowcount 0 -- kill blocking process IF ( @program_name = @program_name_par1 and @waittime > @waittime_threshold and @kill_flag = 'Y') begin select @blocker_spid_msg = 'kill '+ rtrim(convert(varchar(4),@blocker_spid)) exec (@blocker_spid_msg ) end select @total_blockers = count(*) from master..sysprocesses where blocked > 0 select @APP_total_blockers = count(*) , @waittime = max(waittime) from master..sysprocesses where blocked > 0 and program_name = @program_name_par1 END -- end while loop set @blocker_spid_info = CAST(@cnt as char(2)) + ' process(es) killed. Last blocker id info: ' + @blocker_spid_info set @blocker_spid_msg = 'xp_sendmail @recipients = ''' + @email_list + ''' , @subject = ''Blocking Process'', @message = ''' + @blocker_spid_info + '''' exec (@blocker_spid_msg ) END
Caution: Using the kill command is a very dangerous approach to clearing the blocking process because it could potentially corrupt the SQL database if the blocking process is performing a database update. The kill command may also take quite a while to execute if the blocking process has performed a lot of work that the kill process must undo (rollback). For details on the kill command, view SQL Books Online.
In a lot of cases, there are some consequences of blocking that are not so obvious. DBA can spend large amount of time detecting, diagnosing and resolving blocking problems. Appropriate tools and processes can minimize the time required to manage the problems. Company can waste money by buying additional hardware instead of addressing the underlying (blocking) problem.
That’s why it is very important not only to know the standard monitoring connections data (that can be done by using system stored procedures sp_who and sp_who2) but to find out the actual SQL command the process was running at the time. For example, get information about running processes and the last/current SQL command they running if any of them are running more than X number of seconds while usually are short. Connection is identified by the host computer running the process, but theoretically, definition criteria may vary based on the specific conditions, environment, running applications, and so on. My task was to find out what processes are running on server and posting significant load on server. The main problem that it was an intermediate issue and there was no an ability to say when it happened. It may happen 2-3 times a day or may happen 1-2 times a week.
I decided to utilize my standard process which is inserting a snapshot of every process every X number of minutes. And if any defined process is running longer than usually – gather the last command data and in addition activate the job to get a snapshot of all running processes/connections.
There are a lot of articles how to monitor SQL server connections and users. The simplest way is to create a table and store data based on sp_who (or sp_who2) stored procedure or select information from system table sysprocesses. I created table T_SP_WHO_TABLE
CREATE TABLE [dbo].[T_SP_WHO_TABLE] ( [PROCESSES_IDENT_ID] [int] IDENTITY (1, 1) NOT NULL , [PROCESS_ID] [int] NOT NULL , [DATABASE_NAME] [varchar] (25) NOT NULL , [CURRENT_COMMAND] [varchar] (50) NULL , [PROCESS_ID_STATUS] [varchar] (25) NULL , [BLOCKING_PROCESS_ID] [int] NULL , [CPU_TIME] [int] NULL , [DISK_IO] [int] NULL , [LAST_BATCH] [datetime] NULL , [LASTWAITTYPE] [varchar] (64) NULL , [WAITTIME] [int] NULL , [WAITRESOURCE] [varchar] (64) NULL , [HOSTNAME] [varchar] (128) NULL , [PROGRAM_NAME] [varchar] (128) NULL , [NT_USERNAME] [varchar] (128) NULL , [SQL_LOGIN] [varchar] (128) NULL , [OPEN_TRAN] [int] NULL , [LAST_UPDATED_DT] [datetime] NOT NULL DEFAULT getdate() )
Stored procedure P_SP_WHO is running by SQL Server Agent every 5 minutes and insert snapshot of the monitoring data into the table. It gives me some information about connected users and processes. Another procedure P_T_SP_WHO_TABLE_CLEANUP running with daily (or weekly) maintenance job and keeping manageable number of rows in table T_SP_WHO_TABLE by deleting records that are older then X number of days based on the parameter @DAYSBACK.
create procedure P_SP_WHO as BEGIN INSERT INTO T_SP_WHO_TABLE (PROCESS_ID , DATABASE_NAME , CURRENT_COMMAND , PROCESS_ID_STATUS , BLOCKING_PROCESS_ID , CPU_TIME , DISK_IO, LAST_BATCH , LASTWAITTYPE , WAITTIME , WAITRESOURCE , HOSTNAME , PROGRAM_NAME , NT_USERNAME , SQL_LOGIN , OPEN_TRAN ) select spid , convert(varchar(25), case dbid when 0 then 'no database context' else db_name(dbid) end) , cmd , convert(varchar(20), status) , blocked , cpu , physical_io , last_batch , lastwaittype , waittime , convert(varchar(30), waitresource) , convert(varchar(15), hostname) , convert(varchar(30), program_name) , convert(varchar(20), nt_username) , convert(varchar(30), loginame) , open_tran from master..sysprocesses -- where conditions may vary or not exists at all where not ( ltrim(rtrim(hostname)) = '' and status = 'background' ) END create procedure P_T_SP_WHO_TABLE_CLEANUP @DAYSBACK INT as DELETE FROM T_SP_WHO_TABLE WHERE DATEDIFF(DD,LAST_UPDATED_DT, GETDATE()) > @DAYSBACK
To be able storing the command with the last statement I created table H_LAST_COMMAND.
Stored procedure P_MON_CONNECTIONS provide an ability to verify business criteria for the process time delay, store the last command information in case of the reaching a threshold and activate the job to store the snapshot of the processes.
CREATE TABLE [dbo].[H_LAST_COMMAND] ( [LAST_COMMAND_ID] [int] IDENTITY (1, 1) NOT NULL primary key CLUSTERED , [SPID] [int] NOT NULL , [EVENTTYPE] [varchar] (60) NOT NULL , [PARAMETERS] [INT] NULL , [EVENTINFO] [varchar] (600) NULL , [LAST_UPDATED_DT] [datetime] NOT NULL default getdate() ) CREATE procedure dbo.P_MON_CONNECTIONS @SEC_BACK INT = -18 , @hostname varchar(255) , @job_nm varchar(255) as begin declare @maxid int , @cmd1 varchar(8000) , @spid int SET NOCOUNT ON DECLARE @tmp1 table (spid INT, tcmd1 varchar(100), tid int identity(1,1)) create table #lastcommand(eventtype varchar(60) , Parameters int , EventInfo varchar(600) ) IF ( ( select count(*) from master..sysprocesses where hostname like @hostname and open_tran > 0 and login_time < DATEadd(ss, @SEC_BACK, getdate() ) and last_batch < DATEadd(ss, @SEC_BACK, getdate()) ) = 0 ) begin return 0 end insert into @tmp1(spid, tcmd1) select spid , 'dbcc inputbuffer(' + cast(spid as varchar) + ')' from master..sysprocesses /* where condition can vary*/ where status <> 'background' and IsNull(ltrim(hostname),'') <> '' select @maxid = max(tid) from @tmp1 while @maxid > 0 begin select @cmd1 = tcmd1, @spid = spid from @tmp1 where tid = @maxid truncate table #lastcommand insert into #lastcommand(eventtype , Parameters , EventInfo ) exec (@cmd1) insert into h_last_command (spid , eventtype , Parameters, EventInfo ) select @spid, eventtype , Parameters, EventInfo from #lastcommand select @maxid = @maxid - 1 end EXEC msdb.dbo.sp_start_job @job_name = @job_nm SET NOCOUNT OFF return 0 end
To be able to utilize the procedure an another job with one step was created. The job is running endlessly until stopped manually. The step source code is follow:
Declare @delay_in_seconds int Set @delay_in_seconds = 10 WHILE 1=1 BEGIN exec P_MON_CONNECTIONS @SEC_BACK = -15 , @hostname = ‘PROCESS_COMPUTER’ , @job_nm WAITFOR DELAY @delay_in_seconds END
It is possible to wrap the job step into a stored procedure and develop the more sophisticated code with a parameter to define how long job will be running. In addition, for the time delay I created the standard stored procedure, which allows me easier utilize the time delay in many other cases. For example for a data transfer processes.
CREATE procedure dbo.P_TIME_DELAY @delay_in_seconds int -- delay in seconds, max for 1 hour as begin declare @DELAY_TIME varchar(10) , @delay_time_num numeric(6,2) -- check if provided number bigger than 3599 (1 hour) IF ( @delay_in_seconds > 3599 or @delay_in_seconds < 0) begin select raiserror('Error: @delay_in_seconds more than 3599 or less than 0. Value can be between 0 and 3599', 16, 1) return end -- convert seconds set @delay_time_num = @delay_in_seconds set @DELAY_TIME = '00:' + right('0' + cast(FLOOR(@delay_time_num/60) as varchar(2)), 2) + ':' + right('0' + cast( cast( (@delay_time_num - (FLOOR(@delay_time_num/60) * 60) ) as int) as varchar(2)), 2) WAITFOR DELAY @DELAY_TIME End
Most of stored procedures, user defined functions, and tables that are used only by administrative processes (including the ones described above) reside in a separate database created by DBA for system administrator’s needs. This means that there are no users without administrative rights have an ability to execute DBA specifically created stored procedures or functions as well as view or change gathered data.
This article, while is not pretending to deliver the best possible solutions, shows few DBA specific tasks and outline some solutions to solve the blocking problems. I am not discussing the optimization or optimal source code but presented source code is fully functional. The source code can be modified; an error handler and some advanced features can be added based on the specific conditions.