Find all sql statements in a xaction which deadlocked from memory dump

Question

Find all sql statements in a xaction which deadlocked from memory dump

Nikhil Shikarkhane

Default port

Points: 1498
More actions
October 5, 2011 at 9:38 am

#246079

I want to find all sql statements in my deadlocked transaction using memory dumps.
For more details look up : http://stackoverflow.com/questions/7663850/find-all-sql-statements-in-a-xaction-which-deadlocked-from-memory-dump
ps: I apologize for my laziness.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why a memory dump? That seems to be a very inefficient alternative to the usual traceflags, profiler events or extended events for diagnosing deadlocks.

p.s. I apologise for the brief answer, but it matches the question.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

As posted on your StackOverflow question:

You are over engineering this. I don't claim to know all the intricate details of the thread memory but there is no reason for it to keep the statement last executed local, it doesn't need it to perform a rollback of the transaction, that is done using the log records from the transaction log if neccessary. Everything you need to figure out the cause of the deadlock is contained in the deadlock graph XML already. You definitely don't need a memory dump to figure it out. The TSQL execution stack for each of the processes is contained in the <executionStack> element under the process. For example:

<process-list>

<executionStack>

<frame procname="" line="3" stmtstart="118" stmtend="284" sqlhandle="0x03000e0020c96c7ef2b3cd00739f00000100000000000000" />

<frame procname="" line="3" stmtstart="50" stmtend="146" sqlhandle="0x02000000e00b66366c680fabe2322acbad592a896dcab9cb" />

</executionStack>

<inputbuf>

WHILE (1=1)

BEGIN

INSERT INTO #t1 EXEC BookmarkLookupSelect 4

TRUNCATE TABLE #t1

END

</inputbuf>

</process>

<executionStack>

<frame procname="" line="4" stmtstart="120" stmtend="262" sqlhandle="0x03000e0059ed607ff3b3cd00739f00000100000000000000" />

<frame procname="" line="4" stmtstart="82" stmtend="138" sqlhandle="0x020000002a7093322fbd674049d04f1dc0f3257646c4514b" />

</executionStack>

<inputbuf>

SET NOCOUNT ON

WHILE (1=1)

BEGIN

EXEC BookmarkLookupUpdate 4

END

</inputbuf>

</process>

</process-list>

All you have to do is take the sqlhandle and offset information from the frames and you can get the statements in the TSQL stack back out using sys.dm_exec_sql_text(). You won't be able to do this if you are trying to manually execute single statements one at a time to trigger the deadlock, since each stack is only going to have the single statement you executed in it.

Nikhil Shikarkhane Default port Points: 1498 More actions · Answer 3

Background:

We have performance testing env, where we run 12 hour load tests. Next morning we analyze and find out a deadlock. Application executes 7-8 dml statements in a transaction ( we use hibernate ). Since sys.dm_exec_sql_text based will yield result only if its in the cache, we dont get the whole set of dml statements as we are analyzing it the next day (ps: i didnt even try this, when the problem was reported to me after 1 day)

How did we solve this problem today:

1. Setup server side trace

2. Setup event notification which triggers on deadlock and call a sp which stops the trace.

3. From extended event xml report or profiler, we find transaction id and look up the past statements corresponding to same.

How i thought i could solve this problem:

1. Trigger memory dump on extended event "lock_deadlock" with system id included.

2. Somehow try to find history in the thread corresponding to the system id.

Why memory dump:

Because this setup will cause least impact if i have to do it on production.

Alternative:

I trigger a sp on deadlock (via event notification) which captures the sys.dm_exec_sql_text info.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

The Alternative you list is the best way to do what you want done and it would be far less expensive than performing a memory dump to have an activation stored procedure execute for an Event Notification to collect the data. The queue activation is all done asynchronously, where the memory dump is performed synchronously on the firing thread.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Have you tried traceflag 1222? If so, what doesn't it give you in terms of information?

Jonathan's right, a memory dump (even a mini-dump) isn't what I'd call a low-impact event.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nikhil Shikarkhane Default port Points: 1498 More actions · Answer 6

I looked up the dump using

.foreach (obj {s -[1]u 0 l?fffffff "update"}) {.printf "%mu",${obj}}

and i was able to find the 2nd sql stmt but not the first. Thanks for your help Jonathan and Gail.