How does I find what queries were executing in a SQL memory dump?-----Stack

https://blogs.msdn.microsoft.com/askjay/2010/10/03/how-do-i-find-what-queries-were-executing-in-a-sql-memory-dump/JamesaskOctober 3,8

• Note:this Post has been updated in a new post due to an issue found with the steps in this post.  The procedure is the same, and the steps here may be only work with the +-bit dumps. Please read the post located at the address below:

http://blogs.msdn.com/b/askjay/archive/2011/02/18/ Finding-which-queries-were-executing-from-a-sql-memory-dump-revisited.aspx

—————————————————————————————————-

In this post, we'll see if the find out which queries were executing from a SQL Server memory dump. You might has a dump file from a crash of the SQL Service, or if you are taken a diagnostic dump with SQLDumper.

What we do in this post assumes you is working with a full or filtered dump of SQL Server. For more information on dumping SQL Server, the Read this post:

Http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

Some of the objects contained in the dump that is needed to completely understand this process can-only is resolved with  Private symbols. What's this means is so to fully track down the executing query text and you need to being internal to MS with access to "privat E "symbols.

However, after finding the query text with the private symbols, we can quickly get to the query text with public symbols a nd a few specific memory addresses and offsets.

So first, set your public symbol path:

0:000>. Sympath srv*c:\symbols\public* http://msdl.microsoft.com/download/symbols
Symbol Search path is: srv*c:\symbols\public* http://msdl.microsoft.com/download/symbols
0:000>. reload/f sqlservr.exe

Search The Stacks:

0:000> ~* K

You were looking for a stack, which is executing a query. It'll look like this:

Call Site

ntdll! Zwwaitforsingleobject+0xa
kernelbase! waitforsingleobjectex+0x9c
sqlservr! Sos_scheduler::switch+0xc7
sqlservr! Threadscheduler::switchnonpreemptive+0xc6
sqlservr! autoswitchpreemptive::~autoswitchpreemptive+0x39
sqlservr! Sos_task::autoswitchpreemptive::~autoswitchpreemptive+0x26
sqlservr! Np::statuswritenocomplport+0xc3
sqlservr! Snistatuswritenocomplport+0x59
sqlservr! tdssniclient::writestatus+0x99
Sqlservr!write_data+0x1bf
Sqlservr!flush_buffer+0xf3
sqlservr! ckatmaitds::sendrowimpl+0x19c
sqlservr! ces::generaleval+0x91f
sqlservr! Cxstmtquery::ersqexecutequery+0xe3a
sqlservr! cmsqlexeccontext::executestmts<1,1>+0xb6c
sqlservr! cmsqlexeccontext::fexecute+0x593
sqlservr! Csqlsource::execute+0x2f9
sqlservr!process_request+0x370
SQLSERVR!PROCESS_COMMANDS+0X2B2
sqlservr! Sos_task::P aram::execute+0x11b
sqlservr! Sos_scheduler::runtask+0xca&NBSP;
sqlservr! Sos_scheduler::P rocesstasks+0x95&NBSP;
sqlservr! Schedulermanager::workerentrypoint+0x110&NBSP;
sqlservr! Systemthread::runworker+0x60&NBSP;
sqlservr! Systemthreaddispatcher::P rocessworker+0x12c&NBSP;
sqlservr! Schedulermanager::threadentrypoint+0x12f&NBSP;
Msvcr80!_ callthreadstartex+0x17 [f:\dd\vctools\crt_bld\self_64_amd64\crt\src\threadex.c @ 348] &NBSP;
msvcr80!_threadstartex+0x84 [f:\dd\vctools\crt_bld\self_64_amd64\ CRT\SRC\THREADEX.C @ 326]&NBSP;
kernel32! Basethreadinitthunk+0xd&NBSP;
ntdll! rtluserthreadstart+0x21

We is interested in the 3rd parameter of the sqlservr! Cmsqlexeccontext::executestmts call as seen below:

0e 00000000 ' 0f6eee80 00000000 ' 00e90fe3:00000064 ' 00000000

00000001 ' 00000000

00000000 ' 86909380
00000000 ' 00000000
: sqlservr! cmsqlexeccontext::executestmts<1,1>+0xb6c

This is the address of a object, and we need to dump 1 DWORD at a offset of 0x20 into this object:

0:041>

DD 86909380+0x020 L1
00000000 ' 869093a0 869093e0

The address at this offset into the object was a property that contains a pointer (another address) to the buffer that cont  Ains our query text. So we get our address from here:

0:041>

DD 869093e0 L1
00000000 ' 869093e0 86909470

Now this is the address we need. So we dump Unicode string on the this address and we get our query:

0:041>

du 86909470
00000000 ' 86909470 ' ....select * from Sales.salesord"
00000000 ' 869094b0 'erheaderroductlevel '); A

You should is able to follow the approach for most threads executing queries. The signature of the "ExecuteStmts" function (a method of the CMsqlExecContext object) should has the object address we n Eed as the 3rd parameter provided the stack is the same (the method could being overloaded and take something else as the 3rd parameter in a different situation–but I ' d has a to check).

-jay

How does I find what queries were executing in a SQL memory dump?-----Stack