Looking deeper into SQL Server using Minidumps

If you should run into errors in SQL Server itself, minidumps help CSS support you and perform deep level investigation. CSS will sometimes request that you perform such a minidump of the process – the engineer in CSS can then use the dump to analyze the issue.

Minidump files have the extension *.mdmp. In rare cases, you may find some of these files in your SQL Server or Analysis Services directory. CSS may request these files from you if you have a case open with them.

There are several ways to generate a minidump. One way is to use the sqldumper.exe file that ships with SQL Server. You can read aboutsqldumper.exe in:

• KB 827690 – How to use Sqldumper.exe to generate dump files for Windows applications

in Windows 2008 Server there is an easy way to get dumps from the GUI. If you bring up task manager and right click on a process, you get this new option:

But watch out! In the default configuration of Windows 2008 Server you will get a full dump. For a large SQL Server installation with hundreds of GB of memory – generating such a dump can take hours. And while the dump happens, the SQL Server process is frozen.

If you only want the minidump, you can re-configure Windows 2008 Server to generate mini dumps instead of full dumps. This is documented in:

• KB 931673  – How to create a user-mode process dump file in Windows Vista

Now that you know how to create minidumps. Let me show you an example of a curious investigation using a minidump. A question we often get is: Why do I see such high waits for CXPACKET in sys.dm_os_wait_stats. CXPACKET is a wait that SQL Server uses to coordinate parallelism – and you can generally ignore it. But, for those of you curious to know more, minidumps gives you the ability to understand this elusive wait type better.

Recently, I was running an highly parallel INSERT…SELECT statement. I was using the new minimally logged heap operations and the SELECT statement was doing a lot of hash joining. After some time, I saw a lot of tasks blocked on CXPACKET in sys.dm_os_waiting_tasks. I decided to perform a minidump to learn a bit more about SQL Server Parallelism. After opening the dump in WinDbg and running ~*kn I could now see all the thread call stacks in the snahpshot. I saw a lot of threads with this call stack:

Thread: <Many> call stack 
ntdll!ZwWaitForSingleObject 
KERNELBASE!WaitForSingleObjectEx 
sqlservr!SOS_Scheduler::SwitchContext 
sqlservr!SOS_Scheduler::SuspendNonPreemptive 
sqlservr!SOS_Scheduler::Suspend 
sqlservr!EventInternal<Spinlock<153,1,0> >::Wait 
sqlservr!EventInternal<Spinlock<153,1,0>   >::WaitAllowPrematureWakeup 
sqlservr!CXPacketList::RemoveHead 
sqlservr!CXPipe::Pull 
sqlservr!CXTransLocal::AllocateBuffers 
sqlservr!CQScanXProducerNew::AllocateBuffers 
sqlservr!CQScanXProducerNew::GetRowHelper 
sqlservr!FnProducerOpen 
sqlservr!FnProducerThread 
sqlservr!SubprocEntrypoint 
sqlservr!SOS_Task::Param::Execute

From the highlight, it does not take much to guess that this is probably the CXPACKET waiting tasks. Searching for a task that is not waiting, I found this:

Thread: 117 call stack 
msvcr80!memcpy 
sqlservr!RowsetBulk::InsertRow 
sqlservr!CXRowset::InsertRow 
sqlservr!CValRow::SetDataX 
sqlservr!CEs::GeneralEval 
sqlservr!CQScanUpdateNew::GetRow 
sqlservr!CQScanProfileNew::GetRow 
sqlservr!CQueryScan::GetRow 
sqlservr!CXStmtQuery::ErsqExecuteQuery 
sqlservr!CXStmtDML::XretDMLExecute 
sqlservr!CXStmtDML::XretExecute 
sqlservr!CMsqlExecContext::ExecuteStmts<1,0> 
sqlservr!CMsqlExecContext::FExecute 
sqlservr!CSQLSource::Execute 
sqlservr!process_request 
sqlservr!process_commands 
sqlservr!SOS_Task::Param::Execute

Now, you don’t really need source code access to guess what is going on here: SQL Server is inserting the rows and other threads are waiting to feed the insert thread. I can even see that the execution is using what looks like the bulk load function.

If you are curious to learn more about analyzing minidumps there is an excellent article about it found here:

• KB 315263 – How to read the small memory dump files that Windows creates for debugging

Looking deeper into SQL Server using Minidumps

标签：minidump   eem   ret   figure   ase   title   seconds   ring   sys