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
0e 00000000`0f6eee80 00000000`00e90fe3 : 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
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
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.
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. :(
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_t\sql\ntdbms\msql\ods\c_events.cpp @ 1902]
13 2c0 00000000`0fe7f2b0 00000000`00442abb : 00000000`00000000 00000000`00000000 00000000`80f48548 00000000`80f48548 : sqlservr!process_commands+0x2b2 [e:\sql10_main_t\sql\ntdbms\msql\ods\c_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_t\sql\common\dk\sos\include\sos.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.
# 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:\dd\vctools\crt_bld\self_x86\crt\src\threadex.c @ 348]
1d 3f20ffb4 7c80b729 00367d40 3c3869b0 3c3142be msvcr80!_threadstartex(void * ptd = 0x00000000)+0x66 [f:\dd\vctools\crt_bld\self_x86\crt\src\threadex.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. :(