March 11, 2004 at 4:05 pm
One of our clients is reporting the following with an otherwise well-behaved SQL 2000 database application with a VB front-end:
At least once a day our entire system freezes up because of SQL blks. If you do an sp_who you will see basically "dead" threads that just get stuck out mid-process and of course when they die all these other processes back up behind them causing a huge lockup of the system.
Any ideas on where to start looking for the issue? This application performs well at other client sites. They are using a quad Xeon server and all the client machines are running XP.
I know you can't diagnose from afar. I'm just looking for hints. Thanks!
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
March 11, 2004 at 4:44 pm
March 11, 2004 at 9:17 pm
Can you explain the "dead" threads you are seeing? How do you know they are "dead". Are you seeing SPIDs blocking other SPIDs? Could your users possibly be inserting data when someone else is deleting?
You really need to run sp_who2 and have a look to see what processes are doing what. Alternatively run Profiler and check what stored procedures are being run at the same time and go from there.
Hope this helps,
Angela
March 12, 2004 at 6:38 am
If you can see the blocks you can run run DBCC InputBuffer(@spid) to see what command is running for that spid. You should also run sp_lock @spid where @spid ifs the spid of the blocked process. Doing this you will see where it is in a WAIT state and can identify what resource it is waiting on.
Are you using embedded sql or stored procedures for the data access?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2004 at 7:05 am
Yes SPIDs are blocking other SPIDs...when I have to fix the problem I have to run sp_who and trace back the Process ID numbers till I find one that's not being blocked. So I can assume then that thats the one causing the error so i do a kill xxx to clear it and usually the backed up processes will then run. Sometimes if I don't get it fast enough the application will begin to time-out as it starts to run into DAO timeouts.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
March 12, 2004 at 11:11 am
This was not mentioned but may really be the problem. If SQL procedure is being called by Visual Basic the problem could be in VB. When the programmer created an object they failed to drop/release object at termination.
This occured at my last project with very similair results. Found culprit procedure via Profiler. Good luck.
SmithDM
March 16, 2004 at 10:59 am
I wish I could take credit for this script, but I can't. It was done by a friend and business associate of mine. He created it and uses it to track down SQL blocking because it provides more info than sp_who and sp_who2. Let me know what you think:
/*******************************************************************************
FIND BLOCKS IN SQL SERVER
********************************************************************************/
declare @_DBID as int
select @_dbid = 20 --<< CHANGE to Correct DBid number
SELECT a.spid ,
a.blocked ,
substring(convert(sysname, rtrim(c.name)), 1, 10) as ObjectName ,
substring(a.status,1,10) as status ,
substring(a.hostname,1,15) as hostname ,
a.hostprocess ,
substring(a.program_name,1,25) as program_name ,
a.cmd ,
substring(convert(sysname, rtrim(a.loginame)),1,50) as loginname ,
Case b.rsc_type
when 1 then NULL
when 2 then 'DATBASE'
when 3 then 'FILE'
when 4 then 'INDEX'
when 5 then 'TABLE'
when 6 then 'PAGE'
when 7 then 'KEY'
when 8 then 'EXTENT'
when 9 then 'RID'
when 10 then 'APP'
end As resource_type ,
Case b.req_mode
when 0 then NULL
when 1 then 'Schema Stability'
when 2 then 'Schema Modify'
when 3 then 'Shared'
when 4 then 'Update'
when 5 then 'Exclusive'
when 6 then 'Intent Shared'
when 7 then 'Intent Update'
when 8 then 'Intent Exclusive'
when 9 then 'Shared Intent Update'
when 10 then 'Shared Intent Exclusive'
when 11 then 'Update Intent Exclusive'
when 12 then 'Bulk'
when 13 then 'Shared Range Shared Lock'
when 14 then 'Shared Range Update Lock'
when 15 then 'Insert Range Null Lock'
when 16 then 'RangeI_S'
when 17 then 'RangeI_U'
when 18 then 'RangeI_X'
when 19 then 'RangeX_S'
when 20 then 'RangeX_U'
when 21 then 'RangeX_X'
end As lock_request_mode ,
Case b.req_status
when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT'
end as req_status,
a.cpu ,
a.physical_io ,
a.net_address ,
a.waittime,
a.last_batch,
a.open_tran
from master.dbo.sysprocesses a (nolock)
left outer join master.dbo.syslockinfo b (nolock)
on a.spid = b.req_spid
inner join sysobjects c (nolock)
on b.rsc_objID = c.id
where (blocked <> 0
and a.dbid = @_DBID)
or a.spid in (select x.blocked
from master.dbo.sysprocesses x (nolock)
Where blocked <> 0
and a.dbid = @_DBID)
order by blocked desc, spid option(maxdop 1)
==============
After he identifies a SPID that is blocking others, he uses this query to identify the last command:
dbcc inputbuffer(SPID) -- Replacing 'SPID' with the SPID number from the column from the previous query.
==============
Seems good, right?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
March 16, 2004 at 9:02 pm
Here's a link to the PSNWSQL (Pacific Northwest Sql Server Users Group) formerly associated with the acronym PNWSSUG.
http://groups.msn.com/pnwsql/ If you join the MSN community you should be able to get access to the archived presentations.
You might check out the November 05 2003 presentation by Fernando from Solid Quality Learning http://www.SolidQualityLearning.com This guy was mentored by a bunch of the folks writing for SQLMAG found on newstands and on-line. Anyway the presentation talks about using Profile for the kind of problems you are encountering and many others. 29 slides long. The cool thing is once you get used to Profiler you'll be able to capture traces of situations like you are having an replay them for reproducibity and analysis.
Regards,
Peter Evans(__PETER Peter_)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply