Av rating:
Total votes: 19
Total comments: 5

 CODE DOWNLOAD
 send to a friend
 printer friendly version

My dear fellow, you know my methods…

Pop Rivett put down his violin, stretched back in his fireside chair, and stared severely at me. "My dear fellow, you know my methods. The mystery of the rogue SPIDs has been one of the more exacting tasks it has been my fortune to be given.

A process in a complex database occasionally, and apparently randomly, manages to put table locks on vital tables. The result? Several applications are brought to a complete halt. Tracking down the problem was indeed taxing, but through the faculties of deduction and of logical synthesis which I have endeavoured to apply to the task, I consider that I helped the DBA, Lestrade, with some small points in connection with it."

I smiled ruefully, and shook my head. "I was relying on traces and found myself completely inundated and overwhelmed by the detail. I doubt I ever would have solved the mystery!"

"Not so, you are too modest. However, I allow that a certain selection and discretion is necessary for producing a rapid result".

Rivett strummed a few notes on his violin before continuing. "In solving the mysteries of the performance of a database application, one often needs to view a list of the processes currently running on your server; how long each process has been running, what the command was, what is blocking, and how much I/O it is causing."

"How in the name of good fortune would you know all that, Rivett?"

"Elementary, dear fellow. All you need is a stored procedure that will report any SPIDs that are blocking others, the last command executed, and other useful diagnostic attributes. You then need to run it every minute or so on the SQL Server agent, inserting the results into a log table which has a 'timestamp' column, until you catch the process 'in flagrante delicto'. You will then be able to see the command that is doing the blocking, and the processes being blocked."

"Surely, Rivett!" I interjected, "sp_who and sp_who2 will show you the basic details of the processes, and the SPID is just the primary key of the sysprocesses table"

Rivett looked at me pityingly. "Quite! But they will show you all the processes running, not just the blocking processes. Furthermore, they will not tell you what was being executed will they? Try out my sp_SpidByStatus procedure. It requires little skill.

You'll want to call the procedure with a 'blk' parameter to reveal all blocked and blocking spids. The default status is 'runnable' as that is the usual requirement, but you can call the procedure with 'all' as a parameter, so that all SPIDs are displayed. If you call the procedure with a SPID, then just that SPID is displayed.

Examining the sp_SpidByStatus procedure

Take a look at the procedure. I'd show you it on my laptop except the damned battery is flat! Note form will have to suffice…

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_SpidByStatus]')
 				        and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SpidByStatus]
GO
 
Create procedure [dbo].[sp_SpidByStatus]
@status varchar(20) = 'runnable'
as
/*
exec sp_SPIDbyStatus              -- all spids whith status runnable
exec sp_SPIDbyStatus 'sleeping'   -- all spids whith status sleeping
exec sp_SPIDbyStatus 'background' -- all spids whith status background
exec sp_SPIDbyStatus 'sleeping'   -- all spids whith status sleeping
exec sp_SPIDbyStatus 'all'        -- all spids
exec sp_SPIDbyStatus 'blk'        -- all blocked or blocking spids
exec sp_SPIDbyStatus '74'         -- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
 
declare       @cmd varchar(1000)
 
declare @buf varchar(1000) ,
       @id int ,
       @spid int ,
       @maxSpid int
       create table #spid (spid int, command varchar(1000) null)
       create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
       create table #spids (spid int)
       if isnumeric(@status) = 1
       begin
              insert #spids select @status
       end
       else if @status = 'blk'
       begin
              insert #spids 
              select spid from master..sysprocesses where blocked <> 0
              union
              select blocked from master..sysprocesses where blocked <> 0
       end
       else
       begin
              insert #spids select spid from master..sysprocesses 
		where (status = @status or @status = 'all') and ecid = 0
       end
       select        @spid = 0 ,
              @maxSpid = max(spid)
       from   #spids
 
       while @spid < @maxSpid
       begin
              select @spid = min(spid) from #spids where spid > @spid
 
              select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
 
              delete #temp
 
              insert #temp
              exec (@cmd)
 
              select        @id = 0 ,
                     @buf = ''
              select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
 
              from #temp
 
              insert        #spid
              select @spid, @buf
       end
 
       select        blk = case when s.blocked <> 0 then convert(varchar(3),
	     s.blocked) else '   ' end ,
              spid = convert(varchar(4),#spid.spid) ,
              s.physical_io ,
              status = left(s.status,12) ,
              last_batch = convert(varchar(23),s.last_batch,121) ,
              s.cmd ,
              #spid.command ,
              login_time = convert(varchar(23),s.login_time,121) ,
              s.HostName
       from   #spid ,
              master..sysprocesses s
       where  s.spid = #spid.spid
       and    (ecid = 0 or isnumeric(@status) = 1)
       order by s.status, #spid.spid
 
       drop table #spid
       drop table #temp
 
go
 
/* And you will need a table to hold the list of blocking, 
and blocked, processes */
 
CREATE TABLE [dbo].[BlockingSPids](
       [BlockingSpids_ID] [int] IDENTITY(1,1) NOT NULL,
       [blk] [varchar](3)| NOT NULL,
       [spid] [varchar](4)| NOT NULL,
       [physical_io] [int] NOT NULL,
       [status] [varchar](12)| NOT NULL,
       [last_batch] [datetime] NOT NULL,
       [cmd] [nchar](32)| NOT NULL,
       [command] [varchar](1000)| NOT NULL,
       [login_time] [datetime] NOT NULL,
       [HostName] [nchar](256)| NOT NULL,
       [insertionDate] [datetime] NOT NULL 
              CONSTRAINT [DF_BlockingSPids_insertionDate] 
                                  DEFAULT (getdate()),
 CONSTRAINT [PK_BlockingSPids] 
                                  PRIMARY KEY CLUSTERED 
(
       [BlockingSpids_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
 
/* finally, all you will now need to do is to place this code
on the SQL Server agent to catch and identify those Rogue Spids.*/ 
 
insert into BlockingSpids(
blk,spid,physical_io,status,last_batch,cmd,command,login_time,HostName)
       execute sp_SpidByStatus 'blk'

To keep a record of what is happening on your server you can, of course, schedule the call on the SQL Server agent and output the result of this procedure to a table. In short measure, you should have trapped the identity of that rogue process, and the other processes that it is blocking."

"Well, now you explain it, Rivett, it all seems so simple. How silly of me to be overawed by the problem"

"I begin to think", replied Rivett wistfully, "that I make a mistake in explaining. 'Omne ignotum pro magnifico'. My poor reputation, such as it is, will suffer shipwreck if I am so candid."