Looking deeper into SQL Server using Minidumps

https://blogs.msdn.microsoft.com/sqlcat/2009/09/11/looking-deeper-into-sql-server-using-minidumps/

Author:thomas Kejser

Reviewers and Contributors:bob Ward, Michael Thomassy, Juergen Thomas, Hermann Daeubler, Mark Souza, Lubor Kollar, Henk v An der Valk (Unisys) and Peter Scharlock

For advanced troubleshooting and understanding of SQL Server, you can sometimes benefit from creating a dump file of the S Qlservr.exeprocess.

What is a dump? It is a file containing a snapshot of the running Process–and parts or any of the memory space of that process. The snapshot also contains the call stack of every thread, the process has created.

There is major types of dump files:

• A full Dump–this contains the entire process space. A full dump can take a VERY long time to run. If you is only interested in learning more about the SQL Servers internal Structures–do Not the use this type of dump.
• A minidump–this Much smaller file contains the memory for the call stack of all threads, the CPU registers and Informati On about which modules is loaded. If you were just curious about the internals of SQL Server, this is the type of dump you want to create.

Using A debugger, such as windbg, you can analyze a dump file. Remember that is looking at a snapshot in time of the process–but even then, just showing the call stacks can be quite enlightening. IN WINDDG, the command to show all call stacks is: ~*kn. If you is the kind of person who likes to has a mental model of how a product works–minidump can help you deepen your Understanding. A trick I often use are to take three minidumps in a row, waiting a few seconds between each one. By comparing the thread stacks of dumps, I can get a rough idea what threads inside the SQL Server process space is doing . But Remember–these is the threads of the sqlservr.exe process itself– not the session_id that the DMV.

If you should run to errors in SQL Server itself, minidumps help CSS support for you and perform deep level investigation. CSS would 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 has the extension *.mdmp. In the rare cases, you could find some of these files in your SQL Server or analysis Services directory. The CSS may request these files from the If you has a case open with them.

There is several ways to generate a minidump. One-is-to-use the Sqldumper.exe file, 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 the Windows Server there is a easy-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 Server, you'll 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 are want the minidump, you can re-configure Windows Server to generate mini dumps instead for full dumps. This is documented in:

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

Now, how do I know to create minidumps. Let me show you a 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 for SQL Server uses to coordinate parallelism–and you can generally ignore it. But, for those of your curious to know more, Minidumps gives your 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 is doing a lot of hash joining. After some time, I saw a lot of the 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 the This call stack:

thread: <Many> call stack 
ntdll! ZWWAITFORSINGLEOBJECT&NBSP
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::P ull 
sqlservr! cxtranslocal::allocatebuffers 
sqlservr! Cqscanxproducernew::allocatebuffers  
sqlservr! cqscanxproducernew::getrowhelper 
sqlservr! fnproduceropen 
sqlservr! fnproducerthread 
sqlservr! subprocentrypoint 
sqlservr! Sos_task::P aram::execute

For the

from the highlight, it does isn't take much to guess which this is probably the cxpacket waiting tasks. Searching for a task (waiting), I found this:

thread:117 call stack 
msvcr80! MEMCPY&NBSP
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::P aram::execute

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

If you were curious to learn more about analyzing minidumps There was an excellent article on it found here:

• KB 315263–how to read the small memory dump files, Windows creates for debugging

Looking deeper into SQL Server using Minidumps