Getting Started with WinDbg debugging SQL server--

In this article I would like to explore the completely different areas of SQL Server: If you are using WinDbg (from Debugging Tools for Windows) to debug SQL Server. Before we enter the Kuse details, I would like to explain in detail why I chose such an obscure topic to write this article.

reason

When using SQL Server, do you need a debugger like WinDbg? Simple answer: Never needed! SQL Server is a stable product that uses the technology it provides (extended events, DMVS/DMFS) to troubleshoot already, and never use WinDbg. If you have questions, you should first contact PSS before you try to debug a nasty SQL Server problem on your own. What is the reason for using a debugger like WinDbg? For me it's mostly teaching, learning how it works inside SQL Server, and how the relational database engine is implemented. When I deal with SQL Server problems, I sometimes see crazy things in SQL Server, which I'm going to explain. So I know more about SQL Server and how SQL Server works, the better for me.

I use WinDbg only for educational purposes, only in non-production test systems, in order to better understand what happens inside SQL server when the query is executed. For this we also want to know more about the concept of Windows system, user-mode debugging, 64-bit assembly language, and 64-bit machine architecture. Also go through a large number of whitepapers to explain how the relational database engine is implemented. Using WinDbg I can look at SQL Server from the inside, in which way SQL Server implements its functionality, and how these work together. The most important thing is: like the Geek (geeky) to use the WinDbg debugger, everyone is happy.

The Chase

Before we go into how to configure WinDbg in detail for SQL serve debugging, first I want to tell you about the most important DLL files in SQL Server, which we will use when debugging. Let's look at the following image:

We know that SQL Server itself is implemented in an executable sqlservr.exe . During startup,Sqlservr.exe loads multiple DLL files into its process space. From a debug perspective-the following are some of the important DLL files:

• sqldk.dll (SQL Server Development Kit) is currently implementing most of the SQL OS functionality-an OS that is part of SQL Server to handle thread scheduling and memory management (the thread scheduling and Memory mangement).
• Sqlmin.dll implements all the relational engine-related functions, including the storage engine (Storage engine), the data access Methods, the lock manager, the log Manager), an inert writer (lazywriter), and other components. For debugging, it is the most important because it contains most well-known components of SQL Server.
• The Sqllang.dll contains all the functions related to the T-SQL language, as well as the query processor Processor itself.

In order to use WINDBG, you need to first install this debugging tool, which can be downloaded from Microsoft Free: Standalone debugging tool for Windows (WINDBG).

Https://msdn.microsoft.com/zh-cn/windows/hardware/gg463028#Download_windows

(seemingly Microsoft websites are hard to download and are now available separately:

x86-bit version download: "Microsoft Official installation Version"

x64-bit version download: "Microsoft Official installation Version"

）

Once the installation is complete, you can find this program in the Start menu:

When you start WinDbg, make sure that your account has administrator privileges, otherwise you cannot attach to a process like sqlservr.exe. In order to use WinDbg, you need to configure the symbol table (symbols). Symbol table files are used to decode memory addresses, corresponding function names, which makes it easier for us to debug and understand. When you use the symbol table, you want to distinguish between public and private symbol tables. In addition to Microsoft's access to public symbols, we can only access private symbols. Public symbols are a subset of private symbols and do not include something more interesting, such as:

• Data type definition
• Local variable definition
• function parameters

Public symbols provide only your function name-no other information! We need to get used to its huge limitations when compared to private symbols. But on the other hand, Microsoft wants to protect their intellectual property. But just getting the full function name for the call stack (call-stacks), setting breakpoints on them, and debugging them can help you understand how SQL Server works inside. As soon as you configure the WinDbg correctly, for the specified built-in SQL Server you debug, you get the auto-correct public symbol. You can also try native kernel mode debugging (local Kernel mode debugging), you can even get the correct symbol for kernel mode--but this is another thing ... In WinDbg you want to configure the server address of the Microsoft public symbol so that the WinDbg can download them normally. The address is:

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

I ran WinDbg with a batch command, set the correct symbolic server address, and attached it directly to the sqlservr.exe process.

First we switch to the directory where the WinDbg is located at the command prompt:

CD C:\Program files\debugging Tools for Windows (x86)

Then use the following command:

Windbg-y SRV*G:\SYMBOLS*HTTP://MSDL.MICROSOFT.COM/DOWNLOAD/SYMBOLS-PN Sqlservr.exe

You must ensure that only one instance of SQL Server is running, because WinDbg is appended by the process name Sqlservr.exe. If you have multiple instances of SQL Server running, you need to specify the correct PID, which can be obtained from the task Manager.

Use the following command:

Windbg-y srv*g:\symbols*http://msdl.microsoft.com/download/symbols-p 384

The path G:\symbols is the location on your computer to store the downloaded symbol table. The size of that location will change, because you will be downloading a lot of symbol files. My local symbol file now has a size of 1G ... If everything works, you should now be able to attach to Sqlservr.exe:

The program execution is now stopped because you have encountered a breakpoint in the ntdll module. Ntdll is a simple encapsulated DLL provided by the system for user-mode to kernel-mode conversion. This also means that the "every" thread in SQL Server is now stopped and cannot do anything!!! Please do not attempt to attach WinDbg to sqlservr.exe!! in the production environment. If you're running like a sqlservr.exe, press F5 on your keyboard to--sql server and resume running again. If you want to interrupt the execution of the program again, you need to set the breakpoint in Sqlservr.exe 's process space, or ctrl+break through the keyboard shortcut.

If you interrupt the operation with that shortcut key, WinDbg will put you on a particular thread in sqlservr.exe . If you want to analyze a specific memory address or set a specific breakpoint as a further troubleshooting, it is recommended that you do so. A very important command in WinDbg is the x command: It returns all the defined symbols in the module you specify. Let's take a look at the following command:

X sqlmin!*btree*

This windbg command returns all of your list of function names that contain "BTree". In this case, you can set breakpoints on the specified function name for analysis. Returns the function name in the following format:

Module_name!class_name::function_name

such as sqlmin! Btreemgr::seek. if you like to return all functions defined by the Btreemgr class, you can use the following command:

X sqlmin! Btreemgr::*

The x command is a very powerful command to explore the various classes implemented by SQL Server. To save you some homework, answer the following questions:

• Which class implements the lock manager? sqlmin! Lockmanager::*
• Which function is used to obtain and release the latch (latches)? sqlmin! Buf::acquirelatch and sqlmin! Buf::releaselatch
• Which class name implements Sqlos scheduling? sqldk! Sos_scheduler

You are welcome to answer these questions in the comments.

Summary

In this article I have introduced you to WinDbg and how you can use this debugger to attach to SQL Server. As you've seen, the Sqlservr.exe process space contains multiple DLL files, where each DLL implements the various component functions of SQL Server. When you have properly configured the path of the common symbol, you can get the metadata information for each class and function that makes up the SQL Server. The WinDbg x command here is your good helper.

I hope you will like this special article, next time I will show you how to use the WinDbg directive to debug and execute SQL Server queries.

Thanks for your attention!

Getting Started with WinDbg debugging SQL server--