{"id":242,"date":"2007-03-22T00:00:00","date_gmt":"2007-03-22T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/pop-rivett-and-the-case-of-the-rogue-spids\/"},"modified":"2021-09-29T16:22:21","modified_gmt":"2021-09-29T16:22:21","slug":"pop-rivett-and-the-case-of-the-rogue-spids","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/pop-rivett-and-the-case-of-the-rogue-spids\/","title":{"rendered":"Pop Rivett and the Case of the Rogue SPIDs"},"content":{"rendered":"

\"368-Image1.gif\"<\/p>\n

My dear fellow, you know my methods…<\/em><\/p>\n

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.<\/p>\n

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.”<\/p>\n

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!”<\/p>\n

“Not so, you are too modest. However, I allow that a certain selection and discretion is necessary for producing a rapid result”.<\/p>\n

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.”<\/p>\n

“How in the name of good fortune would you know all that, Rivett?”<\/p>\n

“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.”<\/p>\n

“Surely, Rivett!” I interjected, “sp_who<\/b> and sp_who2<\/b> will show you the basic details of the processes, and the SPID is just the primary key of the sysprocesses<\/b> table”<\/p>\n

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 <\/b>procedure. It requires little skill.<\/p>\n

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.<\/p>\n

\"368-Image2.gif\"<\/p>\n

Examining the sp_SpidByStatus procedure<\/em><\/p>\n

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…<\/p>\n

<\/pre>\n
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_SpidByStatus]’)
 \t\t\t\t        and OBJECTPROPERTY(id, N’IsProcedure’) = 1)<\/p>\n
drop procedure [dbo].[sp_SpidByStatus]<\/p>\n
GO<\/p>\n
\n
Create procedure [dbo].[sp_SpidByStatus]<\/p>\n
@status varchar(20) = ‘runnable’<\/p>\n
as<\/p>\n
\/*<\/p>\n
exec sp_SPIDbyStatus              — all spids whith status runnable<\/p>\n
exec sp_SPIDbyStatus ‘sleeping’   — all spids whith status sleeping<\/p>\n
exec sp_SPIDbyStatus ‘background’ — all spids whith status background<\/p>\n
exec sp_SPIDbyStatus ‘sleeping’   — all spids whith status sleeping<\/p>\n
exec sp_SPIDbyStatus ‘all’        — all spids<\/p>\n
exec sp_SPIDbyStatus ‘blk’        — all blocked or blocking spids<\/p>\n
exec sp_SPIDbyStatus ’74’         — an individual spid – also gives subthreads<\/p>\n
select * from master..sysprocesses where spid = 56<\/p>\n
*\/<\/p>\n
set transaction isolation level read uncommitted<\/p>\n
set nocount on<\/p>\n
\n
declare       @cmd varchar(1000)<\/p>\n
\n
declare @buf varchar(1000) ,<\/p>\n
       @id int ,<\/p>\n
       @spid int ,<\/p>\n
       @maxSpid int<\/p>\n
       create table #spid (spid int, command varchar(1000) null)<\/p>\n
       create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))<\/p>\n
       create table #spids (spid int)<\/p>\n
       if isnumeric(@status) = 1<\/p>\n
       begin<\/p>\n
              insert #spids select @status<\/p>\n
       end<\/p>\n
       else if @status = ‘blk’<\/p>\n
       begin<\/p>\n
              insert #spids <\/p>\n
              select spid from master..sysprocesses where blocked <> 0<\/p>\n
              union<\/p>\n
              select blocked from master..sysprocesses where blocked <> 0<\/p>\n
       end<\/p>\n
       else<\/p>\n
       begin<\/p>\n
              insert #spids select spid from master..sysprocesses 
where (status = @status or @status = ‘all’) and ecid = 0<\/p>\n
       end<\/p>\n
       select        @spid = 0 ,<\/p>\n
              @maxSpid = max(spid)<\/p>\n
       from   #spids<\/p>\n
\n
       while @spid < @maxSpid<\/p>\n
       begin<\/p>\n
              select @spid = min(spid) from #spids where spid > @spid<\/p>\n
\n
              select @cmd = ‘dbcc inputbuffer (‘ + convert(varchar(10),@spid) + ‘)’<\/p>\n
\n
              delete #temp<\/p>\n
\n
              insert #temp<\/p>\n
              exec (@cmd)<\/p>\n
\n
              select        @id = 0 ,<\/p>\n
                     @buf = ”<\/p>\n
              select @buf = @buf + replace(replace(s,char(10),’|’),char(13),’|’)<\/p>\n
\n
              from #temp<\/p>\n
\n
              insert        #spid<\/p>\n
              select @spid, @buf<\/p>\n
       end<\/p>\n
\n
       select        blk = case when s.blocked <> 0 then convert(varchar(3),
s.blocked) else ‘   ‘ end ,<\/p>\n
              spid = convert(varchar(4),#spid.spid) ,<\/p>\n
              s.physical_io ,<\/p>\n
              status = left(s.status,12) ,<\/p>\n
              last_batch = convert(varchar(23),s.last_batch,121) ,<\/p>\n
              s.cmd ,<\/p>\n
              #spid.command ,<\/p>\n
              login_time = convert(varchar(23),s.login_time,121) ,<\/p>\n
              s.HostName<\/p>\n
       from   #spid ,<\/p>\n
              master..sysprocesses s<\/p>\n
       where  s.spid = #spid.spid<\/p>\n
       and    (ecid = 0 or isnumeric(@status) = 1)<\/p>\n
       order by s.status, #spid.spid<\/p>\n
\n
       drop table #spid<\/p>\n
       drop table #temp<\/p>\n
\n
go<\/p>\n
\n
\/* And you will need a table to hold the list of blocking, <\/p>\n
and blocked, processes *\/<\/p>\n
\n
CREATE TABLE [dbo].[BlockingSPids](<\/p>\n
       [BlockingSpids_ID] [int] IDENTITY(1,1) NOT NULL,<\/p>\n
       [blk] [varchar](3)| NOT NULL,<\/p>\n
       [spid] [varchar](4)| NOT NULL,<\/p>\n
       [physical_io] [int] NOT NULL,<\/p>\n
       [status] [varchar](12)| NOT NULL,<\/p>\n
       [last_batch] [datetime] NOT NULL,<\/p>\n
       [cmd] [nchar](32)| NOT NULL,<\/p>\n
       [command] [varchar](1000)| NOT NULL,<\/p>\n
       [login_time] [datetime] NOT NULL,<\/p>\n
       [HostName] [nchar](256)| NOT NULL,<\/p>\n
       [insertionDate] [datetime] NOT NULL <\/p>\n
              CONSTRAINT [DF_BlockingSPids_insertionDate] <\/p>\n
                                  DEFAULT (getdate()),<\/p>\n
 CONSTRAINT [PK_BlockingSPids] <\/p>\n
                                  PRIMARY KEY CLUSTERED <\/p>\n
(<\/p>\n
       [BlockingSpids_ID] ASC<\/p>\n
) ON [PRIMARY]<\/p>\n
) ON [PRIMARY]<\/p>\n
\n
\/* finally, all you will now need to do is to place this code<\/p>\n
on the SQL Server agent to catch and identify those Rogue Spids.*\/ <\/p>\n
\n
insert into BlockingSpids(<\/p>\n
blk,spid,physical_io,status,last_batch,cmd,command,login_time,HostName)<\/p>\n
       execute sp_SpidByStatus ‘blk’ <\/p>\n
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.”<\/p>\n
“Well, now you explain it, Rivett, it all seems so simple. How silly of me to be overawed by the problem”<\/p>\n
“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.”<\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":143519,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4728,4168,4729,4150,4183,4252,4727],"coauthors":[],"class_list":["post-242","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-blocking","tag-database","tag-spid","tag-sql","tag-t-sql","tag-t-sql-programming","tag-t-sql-stored-procedure"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/143519"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=242"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/242\/revisions"}],"predecessor-version":[{"id":92569,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/242\/revisions\/92569"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=242"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}