How do I find what queries were executing in a SQL memory dump?


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, but the steps here may only work with 32 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 how to find out which queries were executing from a SQL Server memory dump.   You might have a dump file from a crash of the SQL Service, or you may have taken a diagnostic dump with sqldumper.

What we do in this post assumes you are working with a full or filtered dump of SQL Server.  For more information on dumping SQL Server, 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 are needed to completely understand this process can only be resolved with private symbols.  What this means is that to fully track down the executing query text, you need to be internal to MS with access to “private” symbols.

However, after finding the query text with the private symbols, we can quickly get to the query text with public symbols and 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 are looking for a stack that is executing a query.  It will 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::Param::Execute+0x11b
sqlservr!SOS_Scheduler::RunTask+0xca
sqlservr!SOS_Scheduler::ProcessTasks+0x95
sqlservr!SchedulerManager::WorkerEntryPoint+0x110
sqlservr!SystemThread::RunWorker+0x60
sqlservr!SystemThreadDispatcher::ProcessWorker+0x12c
sqlservr!SchedulerManager::ThreadEntryPoint+0x12f
msvcr80!_callthreadstartex+0x17 [f:\dd\vctools\crt_bld\self_64_amd64\crt\src\threadex.c @ 348]
msvcr80!_threadstartex+0x84 [f:\dd\vctools\crt_bld\self_64_amd64\crt\src\threadex.c @ 326]
kernel32!BaseThreadInitThunk+0xd
ntdll!RtlUserThreadStart+0x21

We are 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 an object, and we need to dump 1 dword at an offset of 0x20 into this object:

0:041> dd 86909380+0x020 l1
00000000`869093a0  869093e0

The address at this offset into the object is a property that contains a pointer (another address) to the buffer that contains 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 this address and we get our query:

0:041> du 86909470
00000000`86909470  "….select * from Sales.SalesOrd"
00000000`869094b0  "erHeaderroductLevel’);..a"

You should be able to follow this approach for most threads executing queries.  The signature of the “ExecuteStmts” function (a method of the CMsqlExecContext object) should have the object address we need as the 3rd parameter provided the stack is the same (the method could be overloaded and take something else as the 3rd parameter in a different situation – but I’d have to check).

-Jay

Comments (8)

  1. Frnak says:

    0e 000000000f6eee80 0000000000e90fe3 : 00000064`00000000

    00000001`00000000

    00000000`86909380

    00000000`00000000

    Can you tell me how do you get this information?

    When I use the k command ,I only get something like this:

    3f20f8f8 01540cc0 sqlservr!CMsqlExecContext::ExecuteStmts<1,1>+0x28d

  2. jamesask says:

    You need to use a parameter with the k command such a 'v' or 'b' –> kv.  This will return the first 3 parameters passed to the function call.

    For information enter the following into windbg:  .hh k

  3. jamesask says:

    I realized this post didn't contain the build of SQL Server.  The dump in this post was from SQL Server 2008 RTM:

    Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)
         Jul  9 2008 14:17:44 
         Copyright (c) 1988-2008 Microsoft Corporation
         Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

    Function signatures can change between versions of any program.

  4. Frank says:

    Thank you!

    When I use the kb command,I got something like this,but I still cannot get my query from the dump.

    0:037> kb

    ChildEBP RetAddr  Args to Child              

    3f20f234 7c92df5a 7c8025db 000009b5 00000000 ntdll!KiFastSystemCallRet

    3f20f238 7c8025db 000009b5 00000000 3f20f26c ntdll!ZwWaitForSingleObject+0xc

    3f20f29c 7c802542 000009b5 000007d0 00000000 kernel32!WaitForSingleObjectEx+0xa8

    3f20f2b0 011e7ced 000009b5 000007d0 6eca977c kernel32!WaitForSingleObject+0x12

    3f20f324 011e7ddb 000007d0 3f20f344 6eca970c sqlservr!Np::StatusWriteNoComplPort+0x9f

    3f20f354 011e7ea2 226dd048 000007d0 3f20f37c sqlservr!SNIStatusWriteNoComplPort+0x82

    3f20f374 012a8ae0 00000000 000007d0 3f20f3d0 sqlservr!TDSSNIClient::WriteStatus+0x6a

    3f20f4a0 0153d30c 229307a8 227a6040 00001000 sqlservr!write_data+0x1a6

    3f20f4d0 0117492e 00000000 0020f704 00000002 sqlservr!flush_buffer+0xdf

    3f20f6a0 015490b6 3f20f6d0 3f20f734 01532f0d sqlservr!CKatmaiTds::SendRowImpl+0x2faf

    3f20f6ac 01532f0d 25adc508 3f20f6d0 25adc9e8 sqlservr!CValOdsRow::SetDataX+0x29

    3f20f6bc 01532d8b 22930d50 25adc0b8 00000000 sqlservr!SetMultData+0x1e

    3f20f734 0154962f 00000000 3f20f7f8 01547825 sqlservr!CEs::GeneralEval4+0xd0

    3f20f740 01547825 25adc508 25adc9e8 00000000 sqlservr!CEs::Eval+0x13

    3f20f7f8 015499af 22930d50 00000000 00000000 sqlservr!CXStmtQuery::ErsqExecuteQuery+0x409

    3f20f85c 015401c3 22930d50 00000000 6eca9ca0 sqlservr!CXStmtSelect::XretExecute+0x268

    3f20f8f8 01540cc0 22930d50 00000000 3f20f900 sqlservr!CMsqlExecContext::ExecuteStmts<1,1>+0x28d

    3f20f9e0 01540686 22930d50 22958400 00000000 sqlservr!CMsqlExecContext::FExecute+0x70e

    3f20fa84 0153cf8c 22930d50 00000000 00000000 sqlservr!CSQLSource::Execute+0x598

    3f20fc08 01539f79 22930aa8 00fdac08 00000000 sqlservr!process_request+0x2f0

    The 3rd parameter is '3f20f900' .When I fellow your step in this post,I got the following:

    0:037> dd 3f20f900+0x020 l1

    3f20f920  2163405c

    0:037> dd 2163405c l1

    2163405c  21634040

    0:037> du 2163405c

    2163405c  "䁀Ⅳ"

    But my build of sql server is different from yours:

    Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

    May be that is why I can not get the right results. 🙁

  5. jamesask says:

    That appears to be the same build –> SQL Server 2008 (RTM), just that you have 32 bit.  That shouldn't matter.  I have seen some situations where the above method doesn't work.  I don't know if they overload the function or what – I haven't dug that deeply into it.  Let's try the long approach here from the batch object in the dump:

    take the 1st parameter at an offset of 0x020 of the 'process_request' frame seen here:

    12       120 00000000`0fe7eff0 00000000`004bebbb : 00000000`818a6fa0 00000000`003e5100 00000000`00000000 00000000`00000000 : sqlservr!process_request+0x370 [e:sql10_main_tsqlntdbmsmsqlodsc_events.cpp @ 1902]

    13       2c0 00000000`0fe7f2b0 00000000`00442abb : 00000000`00000000 00000000`00000000 00000000`80f48548 00000000`80f48548 : sqlservr!process_commands+0x2b2 [e:sql10_main_tsqlntdbmsmsqlodsc_events.cpp @ 2470]

    14       200 00000000`0fe7f4b0 00000000`00440fda : 00000000`00000000 00000000`00000000 00000000`80f501a0 00000000`80f501a0 : sqlservr!SOS_Task::Param::Execute+0x11b [e:sql10_main_tsqlcommondksosincludesos.inl @ 10471]

    0:037> dd 00000000`818a6fa0+0x020 l1

    00000000`818a6fc0  818a6ea0

    then go at an offset of 0x28 from that address:

    0:037> dd 818a6ea0+0x28 l1

    00000000`818a6ec8  80e5fdd0

    next, go at an offset of 0x200 off this address:

    0:037> dd 80e5fdd0+0x200 l1

    00000000`80e5ffd0  84c4ff40

    now, let's take an offset from this address of 0x20:

    0:037> dd 84c4ff40+0x20 l1

    00000000`84c4ff60  84c4e9e0

    finally, we need the address that points to:

    0:037> dd 84c4e9e0 l1

    00000000`84c4e9e0  84e575c0

    and now see if our statement is there by dumping unicode:

    0:037> du 84e575c0

    00000000`84e575c0  "..select * from Sales.SalesOrder"

    00000000`84e57600  "Detail d.. inner join Sales.Sale"

    00000000`84e57640  "sOrderHeader h.. on d.SalesOrder"

    00000000`84e57680  "ID = h.SalesOrderID"

    What we are doing is basically walking through the dump without using private symbol names to get to the dbcc input buffer which has the SQL in it.

    Hope this works for you.

  6. Frank says:

    # ChildEBP RetAddr  Args to Child              

    00 3f20f234 7c92df5a 7c8025db 000009b5 00000000 ntdll!KiFastSystemCallRet

    01 3f20f238 7c8025db 000009b5 00000000 3f20f26c ntdll!ZwWaitForSingleObject+0xc

    02 3f20f29c 7c802542 000009b5 000007d0 00000000 kernel32!WaitForSingleObjectEx+0xa8

    03 3f20f2b0 011e7ced 000009b5 000007d0 6eca977c kernel32!WaitForSingleObject+0x12

    04 3f20f324 011e7ddb 000007d0 3f20f344 6eca970c sqlservr!Np::StatusWriteNoComplPort+0x9f

    05 3f20f354 011e7ea2 226dd048 000007d0 3f20f37c sqlservr!SNIStatusWriteNoComplPort+0x82

    06 3f20f374 012a8ae0 00000000 000007d0 3f20f3d0 sqlservr!TDSSNIClient::WriteStatus+0x6a

    07 3f20f4a0 0153d30c 229307a8 227a6040 00001000 sqlservr!write_data+0x1a6

    08 3f20f4d0 0117492e 00000000 0020f704 00000002 sqlservr!flush_buffer+0xdf

    09 3f20f6a0 015490b6 3f20f6d0 3f20f734 01532f0d sqlservr!CKatmaiTds::SendRowImpl+0x2faf

    0a 3f20f6ac 01532f0d 25adc508 3f20f6d0 25adc9e8 sqlservr!CValOdsRow::SetDataX+0x29

    0b 3f20f6bc 01532d8b 22930d50 25adc0b8 00000000 sqlservr!SetMultData+0x1e

    0c 3f20f734 0154962f 00000000 3f20f7f8 01547825 sqlservr!CEs::GeneralEval4+0xd0

    0d 3f20f740 01547825 25adc508 25adc9e8 00000000 sqlservr!CEs::Eval+0x13

    0e 3f20f7f8 015499af 22930d50 00000000 00000000 sqlservr!CXStmtQuery::ErsqExecuteQuery+0x409

    0f 3f20f85c 015401c3 22930d50 00000000 6eca9ca0 sqlservr!CXStmtSelect::XretExecute+0x268

    10 3f20f8f8 01540cc0 22930d50 00000000 3f20f900 sqlservr!CMsqlExecContext::ExecuteStmts<1,1>+0x28d

    11 3f20f9e0 01540686 22930d50 22958400 00000000 sqlservr!CMsqlExecContext::FExecute+0x70e

    12 3f20fa84 0153cf8c 22930d50 00000000 00000000 sqlservr!CSQLSource::Execute+0x598

    13 3f20fc08 01539f79 22930aa8 00fdac08 00000000 sqlservr!process_request+0x2f0

    14 3f20fd44 015927ee 22930aa8 6eca99e8 216340d8 sqlservr!process_commands+0x2b0

    15 3f20fdb0 01592919 216340d8 00eabaf0 216340d8 sqlservr!SOS_Task::Param::Execute+0x108

    16 3f20fde4 015925ee 216340d8 6eca9a40 00f30040 sqlservr!SOS_Scheduler::RunTask+0xb5

    17 3f20fe18 0111372e 00000000 216340d8 6eca9ac0 sqlservr!SOS_Scheduler::ProcessTasks+0x129

    18 3f20fe98 01113631 216340d8 00838140 7ffdbe28 sqlservr!SchedulerManager::WorkerEntryPoint+0x237

    19 3f20feb0 0156077b 216340d8 6eca9b48 008380d8 sqlservr!SystemThread::RunWorker+0x80

    1a 3f20ff10 0111318a 00000000 6eca9b2c 3c3869b0 sqlservr!SystemThreadDispatcher::ProcessWorker+0x282

    1b 3f20ff74 781329bb 00838000 5a8716e9 3c3869b0 sqlservr!SchedulerManager::ThreadEntryPoint+0x13e

    1c 3f20ffac 78132a47 3c3142be 7c80b729 00367d40 msvcr80!_callthreadstartex(void)+0x1b [f:ddvctoolscrt_bldself_x86crtsrcthreadex.c @ 348]

    1d 3f20ffb4 7c80b729 00367d40 3c3869b0 3c3142be msvcr80!_threadstartex(void * ptd = 0x00000000)+0x66 [f:ddvctoolscrt_bldself_x86crtsrcthreadex.c @ 326]

    1e 3f20ffec 00000000 781329e1 00367d40 00000000 kernel32!BaseThreadStart+0x37

    0:037> dd 22930aa8+0x020 l1

    22930ac8  00000001

    0:037> dd 00000001+0x28 l1

    00000029  ????????

    It is difficult to dig deeply into the dump. 🙁

  7. Sebastian Wain says:

    If you want to intercept the query execution before it crashes I wrote an article with a working example on how to hook into SQL Server. The code retrieves the function address from a PDB. Not all Microsoft SQL Server versions have a PDB but there are ways to find the address if it's not available.

    The article is at: blog.nektra.com/…/sql-server-interception-and-sql-injection-attacks-prevention

    The code is available at: github.com/…/SQLSvrIntercept

  8. jamesask says:

    Sebastian –

    Your post is an interesting post for academic study, but as I'm sure you are aware Microsoft does not support hooking into SQL Server APIs using these methods.  You can read more here:

    support.microsoft.com/…/920925

    I do not recommend it.

    -Jay

Skip to main content