To debug a SQL Server query by using WinDbg

I gave you an introduction to WinDbg in the previous article and how you can attach to SQL Server. Today's article, we go ahead and I'll show you the steps required to debug a SQL Server query using WinDbg. Sounds interesting? Here we go!

Suppose you have a simple query in front of you, and you want to debug that particular query in WinDbg. It sounds simple, but once you start thinking about it, you get a lot of questions:

• How do I identify the correct worker thread on my particular execution query?
• Where should I set breakpoints in Sqlservr.exe?

Let us explain these 2 questions in detail.

identify the correct worker thread

when you execute a query in SQL Server, by default you don't know which thread the query is running on. Fortunately, SQL Server provides os_thread_id columns in the DMV sys.dm_os_threads to tell us. The OS thread ID is used to execute the specified query. Unfortunately, you need to connect multiple tables from sys.dm_exec_requests until sys.dm_os_threads to get the information you need. Let's look at the following query:

1 SELECTR.SESSION_ID, th.os_thread_id fromsys.dm_exec_requests R2 JOINSys.dm_exec_sessions S onr.session_id=s.session_id3 JOINSys.dm_os_tasks T onR.task_address=t.task_address4 JOINSys.dm_os_workers W onT.worker_address=w.worker_address5 JOINSys.dm_os_threads Th onW.thread_address=th.thread_address6 WHERES.is_user_process= 17 GO

interrupt Sqlservr.exe with Ctrl+break in WinDbg. In order to switch to the system thread ID provided by Sys.dm_os_thread, you can use the following WinDbg command:

~~[tid]s

The value of the placeholder TID is the actual system thread id--16 binary value. So you need to convert the os_thread_id column value from sys.dm_os_thread to a 16 binary value, using the command you just mentioned. When your system thread ID is 4910, you should switch to the correct thread with the following WinDbg command:

~~[132e]s

When your query runs, for your search,sys. Dm_os_thread displays only the system thread ID. So there's the next question: How do I get the "correct" system thread ID for an executed query. Here's a tip for me: First I run a simple WAITFOR DELAY command (for example, 1 minutes) and then run the actual query. If you use this method, you need to ensure that 2 T-SQL queries are submitted in 1 batches. Otherwise, SQL OS Scheduler will place WAITFOR statements and actual queries on 2 different threads! Let's look at the actual code:

WAITFORDELAY'00:01:00'SELECTSoh.*, D.* fromSales.SalesOrderHeader SohINNER JOINSales.SalesOrderDetail D onSoh. SalesOrderID=D.salesorderidWHERESoh. SalesOrderID= 71832 andD.salesorderdetailid= 111793GO

During the wait, you need to do the following:

1. from Sys.dm_os_thread for the queries you wait for the system thread ID in different sessions
2. conversion system thread ID is 16 binary
3. Ctrl+break interrupts sqlservr.exe
4. use ~~[tid] command to switch to the correct system thread ID
5. Set breakpoints on the specified thread
6. continue sqlservr.exe Run
7. Wait until the breakpoint is triggered

You want to do all of these operations within the delay time that is caused by the WAITFOR delay statement. If this time is exceeded, the method is unreliable. So at the beginning, you can set a longer delay time with WAITFOR delay until you have experience with this method.

set a "good" breakpoint in Sqlservr.exe

Now that you've got the system thread ID from sys.dm_os_thread , and you've suspended the execution of Sqlservr.exe with WinDbg. The next step is to set a breakpoint in the Sqlservr.exe so that you can debug it in your query and step through it. But what is a good breakpoint? This scenario: each operator in the execution plan is implemented with a separate C + + class that contains different functions. One of the well-known functions is GetRow, which returns a row to the executing iterator. My method is as follows: In the execution plan, try to set breakpoints in the leftmost iterator. From my experience, each select query starts at sqlmin! The Cqueryscan::getrow function call.

It should be useful to start setting breakpoints on specified classes and functions. Of course it takes a long time (when stepping through the code) to guide you through the interesting parts of SQL Server, like the B-Tree manager, or the implementation of a latch/rotation lock. But for the first Test, it is recommended that you set a breakpoint on a particular function. You want to make sure that the breakpoint is set on the correct thread, because you just want to debug your particular query, nothing else! Set breakpoints on the specified thread and symbol name with the BM command:

~tid BM sqlmin! Cqueryscan::getrow

But you also realize that you don't have to provide the system thread ID. The BM command expects a zero-based numeric thread number. When you switch to the correct system thread with ~~[132e]s, you will see the thread number in the lower left corner of the WinDbg:

When WinDbg hints like a 47 thread number, you can use the following command on the correct thread, in sqlmin! The Cqueryscan::getrow function sets a breakpoint:

~47 BM sqlmin! Cqueryscan::getrow

After you set a breakpoint, you can continue to run Sqlservr.exe with F5. After a few seconds (depending on the delay set on the WAITFOR statement) WinDbg should be interrupted at a specific breakpoint:

Now it's time to start: You can use the K command to explore the current call stack, and you can step through the assembly code and see how other functions are called. The dream is how far, your choice is how far (Your choices is endless, and only limited by Your imagination.)

Summary

I hope this article has given you an in-depth introduction to the following content:

How to set breakpoints successfully when debugging a specified query in sqlservr.exe .

Please continue to watch and play "bad" windbg!

Thanks for your attention!

To debug a SQL Server query by using WinDbg