Help filtering important details from system table data
-
I am trying to get to the bottom of timeouts and deadlocks on one of our servers.
I have created an error_logger DB that stores all major errors from the website (timeouts / out of memory)
We have noticed that the timeouts are usually caused by deadlocks, but the SQL involved doesn't seem relevant so I found an KB article on Microsofts web site that has a procedure that is run on a loop throughout the day and interogates the system tables to retrieve relevant info about any locks occuring. Article is
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509
I have tried simulating the deadlock with two competing updates in an transaction to see the info brough back and its alot of stuff I really don't know is relevant. I have modified the proc to only look at my DB but I would like to modify it further so that if any locks causing problems are found then I want to store the information in my error logging DB.
The info I want to store when there are locks causing issues is the following.
Basic details about the processes locked, time they have been waiting, type of lock, the SQL that is causing the lock, the time the process started, if SQL kills a process then which one and the time it was done.
I think that is all I want to get from the the proc if possible as it would be relevant and helpful for statistics later. The problem with the code below is its too long and I don't know what half of it is reporting about so its of no use. If anyone could help with trimming it and making it more relevant then that would be great.
CREATE procedure dbo.sp_blocker_pss80 (@latch int = 0, @appname sysname='PSSDIAG',@databaseName varchar(255)=null)
as
--version 17SP3
if is_member('sysadmin')=0
begin
print 'Must be a member of the sysadmin group in order to run this procedure'
return
end
set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant
declare @databaseID int
--if a database have been passed into specifically be looked at then get DBID
if @databaseName is not null AND @databaseName !=''
select @databaseID=dbid from master.dbo.sysdatabases where name=@databaseName
else
set @databaseID = 0
set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid,
case when convert(varchar(128),hostname) = @appname then 1 else 0 end
from master.dbo.sysprocesses where (blocked!=0 or waittype != 0x0000) AND dbid = case @databaseID when 0 then dbid else @databaseID end
if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
set @time2 = getdate()
print ''
print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))
insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
where blocked not in (select spid from @probclients) and blocked != 0
begin
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121),net_address,
net_library, dbid, ecid, kpid, hostname, hostprocess,
loginame, program_name, nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end
from master.dbo.sysprocesses where dbid = case @databaseID when 0 then dbid else @databaseID end
print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))
print ''
print 'SYSPROC FIRST PASS'
select spid, ecid, waittype from @probclients where waittype != 0x0000
if exists(select blocked from @probclients where blocked != 0)
begin
print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
print 'SPIDs at the head of blocking chains'
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
if @latch = 0
begin
print 'SYSLOCKINFO'
select @time2 = getdate()
select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end,
req_transactionID As TransID, req_transactionUOW As TransUOW
from master.dbo.syslockinfo
print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- Fast not set
print 'DBCC SQLPERF(WAITSTATS)'
dbcc sqlperf(waitstats)
Print ''
Print '*********************************************************************'
Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
Print '*********************************************************************'
declare ibuffer cursor fast_forward for
select distinct cast (spid as varchar(6)) as spid
from @probclients
where (spid <> @@spid) and
((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
or spid in (select blocked from @probclients where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
print 'DBCC INPUTBUFFER FOR SPID ' + @spid
exec ('dbcc inputbuffer (' + @spid + ')')
fetch next from ibuffer into @spid
end
deallocate ibuffer
Print ''
Print '*******************************************************************************'
Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
Print '*******************************************************************************'
declare ibuffer cursor fast_forward for
select distinct cast (dbid as varchar(6)) from @probclients
where dbid != 0
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
set @dbname = db_name(@spid)
set @status = DATABASEPROPERTYEX(@dbname,'Status')
set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
dbcc opentran(@dbname)
else
print 'Skipped: Status=' + convert(nvarchar(128),@status)
+ ' UserAccess=' + convert(nvarchar(128),@useraccess)
print ''
if @spid = '2' select @blocked = 'Y'
fetch next from ibuffer into @spid
end
deallocate ibuffer
if @blocked != 'Y'
begin
print ''
print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'
dbcc opentran ('tempdb')
end
print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)')
GO
Thanks for any advice in advance.
-
how about just adding startupparameters to your sqlserverinstance ?
add -T1204 and -T3605 so deadlock info lis logged into the sqlserver errorlog.
(
1204 Returns the type of lock participating in the deadlock and the current command affect by the deadlock. 1205 Returns more detailed information about the command being executed at the time of a deadlock. 1206 Used to complement flag 1204 by displaying other locks held by deadlock parties 3605 Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.) )
If you cannot stop/start sqlserver, just use dbcc traceon (3605, 1204, -1)
Keep in mind you have to add the startupparameters or this logging will end at a stop of sqlserver.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply