July 10, 2009 at 2:49 am
how can i know the deadlock causing queries? can we do by using DMV's
.
🙂
July 10, 2009 at 3:27 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. The graph will tell you, among other things, what the queries were that were involved in the deadlock.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 3:40 am
I use trace flag 1204, doesn't 1222 give the results in xml? I think it's , -1 after the command and it sets the flag to global rather than local so the flag will stay on the server until you issue the command to stop it or stop the service, I have it switched on at startup.
If you then catch the deadlock quick enough if you have some monitoring on the SQL error log you can use some of the detail from the trace result using an undocumented DBCC PAGE and you can really drill down on the causes of the deadlock.
July 10, 2009 at 3:57 am
BU69 (7/10/2009)
I use trace flag 1204, doesn't 1222 give the results in xml?
1222 is not xml. It's the profiler event "Deadlock graph" that gives xml. The output of 1222 is a lot more detailed and a lot more readable than the info that 1204 gave and is new on SQL 2005. That's why I prefer it to 1204 on 2005 and above.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 4:15 am
Just looked up what I read and it says 'XML-like format' in BOL, that's why I thought it was XML, probably read it too quickly, it also says maybe use both traces, might be a bit much information though, I'll try 1222 and see if it renders using DBCC PAGE useless which would be good as you wouldn't need to be so quick, thanks.
July 10, 2009 at 5:06 am
This is kinda what the beginning of the output looks like (this particular graph is 120 lines long in total)
[font="Courier New"]deadlock-list
deadlock victim=processf14c58
process-list
process id=processc8f198 taskpriority=0 logused=1120 waitresource=KEY: 5:72054587752895472 (f40080bbf0db) waittime=4375 ownerId=513956241 transactionname=user_transaction lasttranstarted=2008-01-15T11:10:30.520 XDES=0xac49ad30 lockMode=S schedulerid=1 kpid=6004 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2008-01-15T11:10:30.520 lastbatchcompleted=2008-01-15T11:10:30.520 clientapp=.Net SqlClient Data Provider hostname=SomeHost hostpid=8547 loginname=SomeLogin isolationlevel=read committed (2) xactid=513956241 currentdb=5 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
executionStack
frame procname=DB1.dbo.Proc1 line=7 stmtstart=284 stmtend=654 sqlhandle=0x03000500036a8132988bf100e79900000000000000000000[/font]
Personally, I use just 1222. The info in there is a superset of that found in 1204. It does identify the objects correctly (towards the end in the resource list section), so there's no real need to use DBCC PAGE any longer
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply