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

Question

Environment:
Sql server 2008 r2
Windows 7 64 bit
Windbg 64bit

What i already know

1. I can find the sql stmts in this transactions by running a server-side profiler trace and stopping it as soon as there is a deadlock. And then searching for the transaction Id in the trace files. But this is a brute force method and cannot always be done on a production environment.

2. I know i should send memory dump to microsoft and get then analyze it. But i want to find our if there is any hope to solve this without private symbols.

Problem:

I create a memory dump using extended events in sql server when 2 transactions deadlock ( lock_deadlock event).

I am manually creating this deadlock scenario via management studio. Lets say 1 of the deadlocked xaction has 2 sql statements in it.

begin tran
  update tabl1 ... -- sql stmt 1
go
  update tabl2 ..  -- sql stmt 2

Now i find this thread in my memory dump and i am able to find out only my sql stmt 2 i.e "update tabl2".

Is there anyway i can look up all the sql stmts that thread had executed in the xaction i.e. in our case "update tabl1.." ?
I want to know what the thread had executed before in the same transaction. Since this xaction is not committed yet at the time of the dump, the values should be somewhere in the thread memory.

Please let me know if i dont make sense here. I have been after this problem for a while and i want to know if this possible or not.

ADDITIONAL INFO

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.

Answer 1

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 element under the process. For example:

  <process-list>
    <process id="process807b6bc8" taskpriority="0" logused="0" waitresource="KEY: 14:72057594038845440 (1a39e6095155)" waittime="4739" ownerId="163539" transactionname="INSERT EXEC" lasttranstarted="2011-10-05T12:29:22.580" XDES="0x82b318b0" lockMode="S" schedulerid="2" kpid="1764" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-10-05T12:29:04.563" lastbatchcompleted="2011-10-05T12:29:04.563" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQL2K8R2-IE2" hostpid="3736" loginname="SQLSKILLSDEMOS\administrator" isolationlevel="read committed (2)" xactid="163539" currentdb="14" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <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>
    <process id="process807b7288" taskpriority="0" logused="228" waitresource="KEY: 14:72057594038910976 (e5b3d7e750dd)" waittime="4742" ownerId="163545" transactionname="UPDATE" lasttranstarted="2011-10-05T12:29:22.587" XDES="0x82b6f950" lockMode="X" schedulerid="2" kpid="12" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-10-05T12:29:10.607" lastbatchcompleted="2011-10-05T12:29:10.600" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQL2K8R2-IE2" hostpid="3736" loginname="SQLSKILLSDEMOS\administrator" isolationlevel="read committed (2)" xactid="163545" currentdb="14" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <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.

UPDATE FROM ADDITIONAL INFO:

The alternative use of Event Notifications with internal queue activation to gather the additional information is the best way to do what you want done and it would be far less expensive than performing a memory dump. An activation stored procedure executes for an Event Notification to collect the data asynchronously, where the memory dump is performed synchronously on the firing thread in Extended Events as an Action.