PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Pop Rivett and the Case of the Rogue SPIDs

A process in a complex database occasionally, and apparently randomly, manages to put table locks on vital tables. Several applications are brought to a complete halt. Armed with a T-SQL stored procedure, a violin and a keen investigative spirit, Pop Rivett tracks down the rogue SPIDs that are causing all the problems...

368-Image1.gif

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.

368-Image2.gif

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.”