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

environment:
sql server 2008 r2
windows 7 64 bit
windbg 64bit

what know

1. i can find sql stmts in transactions running server-side profiler trace , stopping there deadlock. , searching transaction id in trace files. brute force method , cannot done on production environment.

2. i know should send memory dump microsoft , analyze it. want find our if there hope solve without private symbols.

problem:

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

i manually creating deadlock scenario via management studio. lets 1 of deadlocked xaction has 2 sql statements in it.

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

now find thread in memory dump , able find out sql stmt 2 i.e "update tabl2".

there anyway can sql stmts thread had executed in xaction i.e. in our case "update tabl1.." ?
i want know thread had executed before in same transaction. since xaction not committed yet @ time of dump, values should somewhere in thread memory.

please let me know if dont make sense here. have been after problem while , want know if possible or not.

additional info

background:
have performance testing env, run 12 hour load tests. next morning analyze , find out deadlock. application executes 7-8 dml statements in transaction ( use hibernate ). since sys.dm_exec_sql_text based yield result if in cache, dont whole set of dml statements analyzing next day (ps: didnt try this, when problem reported me after 1 day)

how did solve problem today:
1. setup server side trace
2. setup event notification triggers on deadlock , call sp stops trace.
3. extended event xml report or profiler, find transaction id , past statements corresponding same.


how thought solve problem:
1. trigger memory dump on extended event "lock_deadlock" system id included.
2. somehow try find history in thread corresponding system id.


why memory dump:
because setup cause least impact if have on production.

you on engineering this. don't claim know intricate details of thread memory there no reason keep statement last executed local, doesn't need perform rollback of transaction, done using log records transaction log if neccessary. need figure out cause of deadlock contained in deadlock graph xml already. don't need memory dump figure out. tsql execution stack each of processes contained in element under process. 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 #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 have take sqlhandle , offset information frames , can statements in tsql stack out using sys.dm_exec_sql_text(). won't able if trying manually execute single statements 1 @ time trigger deadlock, since each stack going have single statement executed in it.

update additional info:

the alternative use of event notifications internal queue activation gather additional information best way want done , far less expensive performing memory dump. activation stored procedure executes event notification collect data asynchronously, memory dump performed synchronously on firing thread in extended events action.