SQL Server Performance

Capturing Blocking Processes

Discussion in 'Contribute Your SQL Server Scripts' started by druer, Nov 9, 2005.

  1. druer New Member

    Long ago I create a script that ran every minute and would email me if there were processes that were being blocked on the server. After a while of capturing that information and realizing that in and of itself the data was pretty useless, I modified the script as follows so that whenever processes were being blocked it would output the actual command of the process that was causing the blocking. The script does assume that there is already a table in Master created called BlockingProcesses that will hold the values. I did this primarily because there were several third party stored procedures running that the performance on is terrible, and as they hit very important tables they were constantly causing blocking to occur. With the documentation in hand to show that they were in fact the commands causing the blocking to occur, and how often the blocking did occur I was able to put pressure on them to make the changes I was suggesting. (I realize that running it every minute doesn't reflect accuracy in numbers as blocking could have occured before the job was invoked and cleared up by then, but the goal was to show the basic "look blocking is occuring at least X times per day, and there are only 2 commands that are causing it..." now fix them.) Not sure if anyone else would have a need for anything like this, but as I've learned so much from reading articles and other forum suggestions I figured that I would share this. <br /><pre id="code"><font face="courier" size="2" id="code"><br />USE MASTER<br />declare @BlockingProcesses TABLE (spid smallint, kpid smallint, blocked smallint, dbid smallint, hostname nchar(256))<br /><br />insert into @BlockingProcesses<br />select spid, kpid, blocked, dbid, hostname <br />from sysprocesses where spid in (select distinct blocked from sysprocesses where blocked &gt; 0)<br /><br />declare @count as int<br />select @count = count(*) from @BlockingProcesses<br />if @count &gt; 0 <br />begin<br />exec master..xp_sendmail @recipients = 'names to email', @subject = '[FYI] Blocked Processes were found on the server'<br /><br />declare @WhenHappened as datetime<br />declare @spid as smallint<br />declare @kpid as smallint<br />declare @blocked as smallint<br />declare @dbid as smallint<br />declare @hostname as nchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @commandtype as nvarchar(30)<br />declare @command as nvarchar(255)<br /><br />declare curBlocking cursor for<br />select * from @BlockingProcesses<br /><br />open curBlocking<br /> FETCH NEXT FROM curBlocking INTO @spid, @kpid, @blocked, @dbid, @hostname<br /><br /> WHILE @@FETCH_STATUS = 0<br /> BEGIN<br />CREATE TABLE #InputBuffer(EventType nvarchar(30), Parameters int, EventInfo nvarchar(255)) <br /><br />DECLARE @ExecStr as nvarchar(255)<br /> SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@spid) + ')'<br /> INSERT INTO #InputBuffer <br />EXEC (@ExecStr)<br />SELECT @CommandType = EventType, @Command = EventInfo FROM #InputBuffer<br /><br />DROP TABLE #InputBuffer<br /><br />insert into BlockingProcesses <br />values (GetDate(), @spid, @kpid, @blocked, @dbid, @hostname, @CommandType, @Command)<br /><br /> FETCH NEXT FROM curBlocking INTO @spid, @kpid, @blocked, @dbid, @hostname<br /> END<br /><br />CLOSE curBlocking<br />DEALLOCATE curBlocking<br />end<br /><br /></font id="code"></pre id="code"><br />

Share This Page