(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan – you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog posting I want to dig into a completely different area in SQL Server: how to debug SQL Server with WinDbg – the debugger that comes with the Debugging Tools for Windows. Before we go into the nasty details, I want to explain in a bit in more detail why I have chosen such an obscure topic to blog about.

Why?

Why on earth do you ever need a debugger like WinDbg when working with SQL Server? The short answer: NEVER! SQL Server is a stable product and you should be always fine with the troubleshooting techniques (Extended Events, DMVs/DMFs) provided by it, without ever having a need for WinDbg. And if you are in doubt, you should always contact PSS first, before trying to debug a nasty SQL Server problem yourself. What are the reasons for using a debugger like WinDbg? For me it’s mainly about education, and learning *how* things are working internally in SQL Server, and *how* relational database engines are implemented. When I’m dealing with customer problems, I sometimes see crazy things happening within SQL Server, which I have to explain. Therefore the more I know about SQL Server, and how SQL Server works, the better it is for me and my customers.

I’m using WinDbg ONLY for educational purposes on non-production test systems to get a better understanding of what happens within SQL Server when executing queries. And as a side-effect I’m learning a bunch of additional concepts about the Windows OS, about user-mode debugging, the X64 assembly language, and the x64 machine architecture. Over the last 6 months I have also read a huge amount of books, and research whitepapers that describe *how* relational database engines are implemented. With WinDbg I can have a look into SQL Server, in which way some things are implemented, and how things are working together. And the most important thing: using a debugger like WinDbg is just geeky, and everyone loves it

Let’s get started

Before we go into the details how to setup WinDbg for SQL Server debugging, I first want to give you a high level overview about the most important SQL Server DLL files, that you will use during debugging. Let’s have a look on the following figure.

As you know, SQL Server itself is implemented in the executable sqlservr.exe. During startup, sqlservr.exe load multiple DLL files into its process space. The most important ones – from a debugging perspective – are the following ones:

• The DLL file sqldk.dll (SQL Server Development Kit) implements by far the largest part of SQL OS – the OS that is part of SQL Server which handles thread scheduling and memory management.
• sqlmin.dll implements everything regarding the relational engine itself, including the Storage Engine, Data Access Methods, Lock Manager, Log Manager, LazyWriter, and other components. This file is one of the most important ones for debugging, because it contains the major well-known components of SQL Server.
• In addition you will also have the DLL file sqllang.dll that contains everything regarding the T-SQL language, and the Query Processor itself.

To use WinDbg you have to install the Debugging Tools for Windows, which can be downloaded for free from Microsoft. After you have installed them, you get in the folder

C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64

the executable windbg.exe that is the debugger itself. When you start WinDbg, you have to make sure that you start it with administrative privileges, because otherwise you have no chance to attach to a process like sqlservr.exe. To use WinDbg effectively, you also have to configure so-called symbols. The symbol files are used to decode memory addresses to their corresponding function names for easier debugging and understanding. When you are working with symbols, you have to distinguish between public and private symbols. Every person outside of Microsoft *only* has access to the public symbols. The public symbols are just a subset of the private symbols, excluding more interesting things like:

• Data type definitions
• Local variable definitions
• Function parameters

The public symbols provide you only with function names – almost nothing more! That’s a huge restriction that we have to live with (compared to the private symbols). But on the other hand Microsoft wants to protect their intellectual property. But getting complete function names for call-stacks, setting breakpoints on them, and debugging through them will greatly help you get a better understanding of what happens within SQL Server. As long as you have configured WinDbg correctly, you will get automatically the correct public symbols for your specific SQL Server build that you are debugging. You can also try local Kernel Mode Debugging, and you will even get the correct symbols for the kernel mode – but that’s a different (long) story… Within WinDbg you just have to configure the internet address of the public symbol server of Microsoft, where WinDbg can download them. The address is 

http://msdl.microsoft.com/download/symbols

I’m using a simple batch file, which launches WinDbg, sets the correct address of the symbol server, and attaches directly to the process sqlservr.exe. The used command line is as follows:

windbg -y srv*g:\symbols*http://msdl.microsoft.com/download/symbols -pn sqlservr.exe

You must make sure that only one instance is SQL Server is running, because WinDbg is attached by the process name sqlservr.exe. If you have multiple instances of SQL Server running, you have to specify the correct PID, which you can get from Task Manager. The path g:\symbols is the location on your computer where the downloaded symbol will be stored. Size that location accordingly, because you can download a huge amount of symbol files over the time. My local symbol store has currently a size of around 1 GB… If everything went fine, you should by now be attached to sqlservr.exe:

The program execution has currently stopped, because you hit a breakpoint in the module ntdll. ntdll is a simple wrapper DLL provided by the OS, which performs the transition from user mode into the kernel mode. This means also that now *every* thread within SQL Server is stopped, and no more work is done anymore!!! NEVER EVER try to attach WinDbg to sqlservr.exe in a production environment! If you want to resume the execution of sqlservr.exe just hit the F5 key on your keyboard – SQL Server is running again. If you want to break the program execution again, you have to set a specific breakpoint somewhere in the process space of sqlservr.exe, or you can also break the current execution by the keyboard shortcut CTRL + BREAK.

If you break the execution with that shortcut, WinDbg just puts you onto a specific thread, somewhere within sqlservr.exe. That’s only recommended if you want to analyze a specific memory address, or if you want to set a more concrete breakpoint for further troubleshooting. One very important command in WinDbg is the x command: it returns you all symbols that are defined in a specific module. Let’s have a look at the following command:

x sqlmin!*BTree*

This WinDbg command returns you all function names that have the word “BTree” in their name. You are just able to analyze and return the various function names on which you can set a breakpoint. The format of the returned function names is as follows: module_name!class_name::function_name, like sqlmin!BTreeMgr::Seek. If you want to return all functions defined by the class BTreeMgr, you can use the following command:

x sqlmin!BTreeMgr::*

The x command is a very powerful one to explore the various classes that SQL Server implements. To give you some home work, try to answer the following questions:

• In which class is the Lock Manager implemented?
• What functions are used to acquire and release latches?
• What’s the name of the class that implements the SQLOS Scheduler?

Please feel free to post your answers as a comment.

Summary

In this blog posting I have given you a brief introduction to WinDbg, and how you can use this debugger to attach it to SQL Server. As you have seen, the process space of sqlservr.exe consists of multiple DLL files, where every DLL implements a larger set of components of SQL Server. When you have configured the path to the public symbols in the correct way, you are able to retrieve a lot of meta data information about the various classes and functions that are part of SQL Server. The WinDbg command x is here your friend.

I hope that you have enjoyed this very specific blog posting, and next week I will show you, how you can debug and execute a SQL Server query instruction by instruction within WinDbg.

Thanks for reading!

-Klaus