SQL Server debugging with Windbg–an Introduction

Klaus Aschenbrennerklaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the. NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004-2005 Klaus is entitled with the MVP Award from Microsoft to his tremendous support in the. NET Commu Nity. Klaus have also written the book Pro SQL Server Service Broker which is published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www. sqlpassion.at. He also twitters at Http://twitter.com/Aschenbrenner.SQL Server debugging with Windbg–an Introduction

• Posted on 5
• Comments
• Briefcase
• Print

(Be sure to checkout the free sqlpassion performance Tuning Training Plan–you get a weekly e-mail packed with all the ESS Ential 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 , Haven 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 had chosen such an obscure topic to Blog about.

Why?

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

I ' m using WINDBG only for educational purposes on non-production test Systems to get a better understand ing 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 had also read a huge amount of books, and the whitepapers that describe *how* relational da Tabase engines is implemented. With WINDBG I can has a look into SQL Server, in which some things is implemented, and how things is working togeth Er. And the most important thing:using a debugger like WinDbg are just geeky, and everyone loves it

Let ' s get started

Before we go into the details what to setup WINDBG for SQL Server debugging, I first want to give you a high level overview About the most important SQL Server DLLs files, that's you'll use during debugging. Let ' s has a look on the following figure.

As you know, SQL Server itself are 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 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 Acces S 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 Serv Er.
• In addition you'll also has the DLL file Sqllang.dll that contains everything regarding the T-SQL language, an D the Query Processor itself.

To use WINDBG you has to install the debugging Tools for Windows, which can is downloaded for free from Microsoft. After you are installed them, you get in the folder

C:\Program Files (x86) \ Windows kits\8.1\debuggers\x64

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

• Data type definitions
• Local Variable Definitions
• Function parameters

The public symbols provide your only with function Names–almost nothing more! That's a huge restriction that we had 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 would greatly help you get a better understanding of what happens within SQL Server. As long as you had configured WINDBG correctly, you'll get automatically the correct public symbols for your specific S QL Server build that is debugging. You can also try local Kernel Mode debugging, and you'll even get the correct symbols for the Kernel mode–but that ' s a Different (long) story ... Within WINDBG you just has to configure the Internet address of the public symbol server of Microsoft, where WINDBG can D Ownload 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 a instance is a SQL Server is running, because WINDBG are attached by the process name SQL Servr.exe. If you had multiple instances of SQL Server running, you had to specify the correct PID, which you can G ET from Task Manager. The path G:\symbols is the location of the your computer where the downloaded symbol would 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 is attached to sqlservr.exe:

The program execution have currently stopped, because you hits 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 mo De. This means also so now *every* thread within SQL Server are stopped, and no more work are 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 hits the F5 key on your keyboard–sql Server is running Again. If you want to broke the program execution again and you have to set a specific breakpoint somewhere in the process space of sqlservr.exe, or you can also breaks 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 was the X command:it returns you all symbols that was defined in a specific module. Let ' s has a look at the following command:

X sqlmin!*btree*

This WINDBG command returns your all function names that has the word "BTree" in their name. You is 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!b Treemgr::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 your some home work, try to answer the following questions:

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

Feel free to post your answers as a comment.

Summary

In this blog posting I has given you a brief introduction to WINDBG, and how can use this debugger to attach it to SQ L 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 had configured the path to the public symbols in the correct, you were able to retrieve a lot of meta data in Formation about the various classes and functions that is part of SQL Server. The WINDBG command x is here your friend.

I hope that you had enjoyed this very specific blog posting, and next week I'll show you, how can debug and execute A SQL Server query instruction by instruction within WINDBG.

Thanks for reading!

-klaus

SQL Server debugging with Windbg–an Introduction