Deadlocks and Profiler - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Deadlocks and Profiler

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2008-02-25 : 15:29:26

I have a couple 3rd party vendor applications plagued by deadlocks.
The vendors don't have a handle on the problems. I need to identify the sql for the deadlocking spids. Most help on this topic is
sketchy. "Run Profiler and analyze it and find the problem"

I'm afraid I could use a little more detail. Books Online is also
not a big help. It gives some suggestions on event classes to
capture, then says,
"group the captured data by ClientProcessID"
and " expand both connections involved in the deadlock"
That might be helpfull if I knew what those meant. But I don't. It never does get around to saying how to find the SQL involved.

So, I would very much appreciate detailed instructions or a pointer to a good white paper.

This forum has a couple references to the aba_lockinfo stored procedure at http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This thing is very nice, but deals with blocking in general, not deadlocking.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-25 : 15:35:58

Or enable trace flag 1024, it'll log details in sql server log.

dbthj
Posting Yak Master

143 Posts

Posted - 2008-02-25 : 17:06:49

I assume you mean 1204 rather than 1024. That works pretty well,
and it's easy too. Thanks. I still have some questions though.

Consider the error output below. I suspect that the sql stmts involved were in Stored Procedures, though I can't say for sure.
If they were, would this output say which SPs were involved? If not,
is there a way to tell which SPs were fired?

Also, I am still interested in seeing a Profiler solution to
deadlock analysis. Any hints would be appreciated.

2008-02-25 15:08:16.60 spid4
Deadlock encountered .... Printing deadlock information
2008-02-25 15:08:16.60 spid4
2008-02-25 15:08:16.60 spid4 Wait-for graph
2008-02-25 15:08:16.60 spid4
2008-02-25 15:08:16.60 spid4 Node:1
2008-02-25 15:08:16.60 spid4 KEY: 7:72387327:1 (b601c09265b7) CleanCnt:2 Mode: X Flags: 0x0
2008-02-25 15:08:16.60 spid4 Grant List 0::
2008-02-25 15:08:16.60 spid4 Owner:0x24fed220 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:151 ECID:0
2008-02-25 15:08:16.60 spid4 SPID: 151 ECID: 0 Statement Type: SELECT Line #: 1
2008-02-25 15:08:16.60 spid4 Input Buf: Language Event: DECLARE @INPAR1 nvarchar(4000)
DECLARE @INPAR2 nvarchar(4000)
SELECT TRIGGER_NAME FROM QRTZ_TRIGGERS WHERE TRIGGER_NAME = N'triggerForJobnotification/2215' AND TRIGGER_GROUP = N'notification'
2008-02-25 15:08:16.60 spid4 Requested By:
2008-02-25 15:08:16.60 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:136 ECID:0 Ec:(0x65575548) Value:0x52426b80 Cost:(0/1A8)
2008-02-25 15:08:16.60 spid4
2008-02-25 15:08:16.60 spid4 Node:2
2008-02-25 15:08:16.60 spid4 KEY: 7:216387840:1 (650456ec6c37) CleanCnt:2 Mode: X Flags: 0x0
2008-02-25 15:08:16.60 spid4 Grant List 1::
2008-02-25 15:08:16.60 spid4 Owner:0x24f3dce0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:136 ECID:0
2008-02-25 15:08:16.60 spid4 SPID: 136 ECID: 0 Statement Type: DELETE Line #: 1
2008-02-25 15:08:16.60 spid4 Input Buf: Language Event: DECLARE @INPAR1 nvarchar(4000)
DELETE FROM QRTZ_FIRED_TRIGGERS WHERE ENTRY_ID = N'prmbp0031203369933088'
2008-02-25 15:08:16.60 spid4 Requested By:
2008-02-25 15:08:16.60 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:151 ECID:0 Ec:(0x4ADA3548) Value:0x3a7c6780 Cost:(0/1DC)
2008-02-25 15:08:16.60 spid4 Victim Resource Owner:
2008-02-25 15:08:16.60 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:136 ECID:0 Ec:(0x65575548) Value:0x52426b80 Cost:(0/1A8)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-25 : 20:16:25

Yes 1204, and you can query syscomments table to find out sp name.

dbthj
Posting Yak Master

143 Posts

Posted - 2008-02-26 : 10:54:19

Well, yes
IF the query is in a stored procedure and the text is not a
resolved parameter. In the example I posted, one DML statement
includes 'DELETE FROM QRTZ_FIRED_TRIGGERS WHERE'. If that string
does not appear in the text column of syscomments, does that
mean that the statement defintitely did not come from a stored
procedure?

There's a lot of hunt-and-peck involved in this.

I really like the infomation in the error log even though
the sql call is not complete. but it is not tidy and not
queryable. And it does not indicate whether or not you might
have luck in querying the syscomments table.

Is there a way to get this same information (without a lot of extra gargabe) in a table (like with Profiler, perhaps)?

dbthj
Posting Yak Master

143 Posts

Posted - 2008-02-26 : 11:31:35

I'm getting the picture now. If one of the offending requests
is an SP, then the error log seems to give it's name, but not
does not identify the DML involved.

There's gotta be a cleaner way to do this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources