Debugging a SQL Server query with WINDBG

Debugging a SQL Server query with Windbgmay 13, 2014 • Klaus Aschenbrenner5 Comments (Be sure to checkout the free sqlpassion performance Tuning Training Plan-you get a weekly e-mail packed with all the Essential knowledge you need to know about performance tuning on SQL Server.)

In my last blog posting I gave your a general introduction to WINDBG, and told what you can attach the debugger to SQL S Erver. In today's blog posting, we'll go into a little more detail, and I'll show you the steps your need to live debug a SQL Server query with WINDBG. Sound interesting? Let ' s start!

Imagine you has a simple SQL query in front of your, and you want to debug that specific query within WINDBG. Sounds like a trivial task, but as soon as you start thinking about it, various questions arise:

• How can I identify the correct worker thread within WINDBG, on which my specific query is executed?
• Where within Sqlservr.exe should I set a breakpoint?

Let's concentrate on both questions in a little bit more detail.

Identifying the correct worker thread

When you execute a query within SQL Server and you have the default no idea which thread, the query runs on. Fortunately SQL Server provides us with column os_thread_id in the DMVsys.dm_os_threads. That's the ID of the OS thread that executes a specific query. Unfortunately need to join between multiple tables to get from sys.dm_exec_requestsdown to Sys.dm_os_thre Ads. Let ' s has a look at the following query.

1234567

select r.session_id, Thos_thread_id from Span class= "crayon-i" >sys. Dm_exec_requests r join sys.dm_exec_sessions s on r. Session_id = Ssession_id join sys.dm_os_tasks t on r. Task_address = T< Span class= "Crayon-sy". task_address join sys.dm_os_workers w on t. Worker_address = W< Span class= "Crayon-sy". worker_address JOIN sys. Dm_os_threads Th on W. thread_address = Th. Thread_address WHERE S. is_user_process = 1 GO

Sqlservr.exe with WINDBG (CTRL + Break). To switch to a specific thread based in the OS thread ID that sys.dm_os_threadsReports, you can use the following WINDBG command:

~~[tid]s

The place holder value tid is the actual OS thread Id–as a hex value. Therefore you are to convert the value of the column os_thread_id from sys.dm_os_threads to a hex value , and use it with the above mentioned command. When your OS thread ID was 4910, you would use the following WINDBG command to switch to the correct thread:

~~[132e]s

sys.dm_os_threads only shows your OS thread ID for your query while your query is running. Therefore the next question Arises:how can I get the *current* OS thread ID for a executing query? I ' m using here a simple trick here:in the first step I ' m running a simple WAITFOR DELAYcommand (e.g. 1 minute), and afterwards I ' m running the actual query. If You use the This approach, you should have a sure to submit both T-SQL queries to SQL Server within 1 batch. Otherwise the SQL OS Scheduler may put the WAITFORstatement and your actual query on 2 different threads! Let ' s has a look at the actual code:

12345678910

WAITFOR DELAY ' 00:01:00 ' SELECT Soh. *, d. *from Sales. SalesOrderHeader Soh inner join sales. Salesorderdetail d On soh. Salesorderid = d< Span class= "Crayon-sy". salesorderid WHERE soh. SalesOrderID = 71832 and d. Salesorderdetailid = 111793 GO

During The wait interval you has to perform the following actions:

1. Retrieve in a different session the OS thread ID for your (waiting) query from sys.dm_os_threads
2. Convert the OS thread ID to a hex value
3. Break sqlservr.exe with CTRL + break
4. Switch to the correct OS thread with the command ~~[tid]s
5. Set a breakpoint on the specific thread
6. Continue the execution of Sqlservr.exe
7. Wait until the breakpoint is reached

You have to perform all these actions within the delay, which is causing with the WAITFOR delay statement. If you take the longer, that approach won't work reliably. Therefore I sugest that you wait a little bit longer in the beginning with the WAITFOR DELAY statement, until There are some experience with that approach.

Setting a "good" breakpoint within Sqlservr.exe

You had now retrieved the OS thread ID from sys.dm_os_threads , and you had suspended the execution of sqlservr.exe  with WinDbg. In the next step you had to set a breakpoint within Sqlservr.exe, so the can debug and single-step through your quer Y. What's a good break point? It depends;-). Every operator in an execution plan is implemented as a separate C + + class, which contains different functions. One well-known function is  GetRow , which returns one row to the upstream iterator in the execution. My approach is the following one:trying to set a breakpoint in one of the Left-most iterators in the execution plan. As far as I had seen from my experiments, every SELECT query starts with a function call to sqlmin! Cqueryscan::getrow .

Setting a breakpoint on this specific class and function should work very well for the beginning. Of course it would take you a very long time (when single-stepping through the code) until your hit interesting parts of the SQL Server Engine, like the B-tree Manager, or the latching/spinlocking implementation. But for the first few experiments you should is fine with a breakpoint on that specific function. You have a to do sure to set the breakpoint on the correct thread because you just want to debug your specific query, and Nothing else! Setting a breakpoint on a specific thread and symbol name is do with the BM command:

~tid BM sqlmin! Cqueryscan::getrow

But if you had to be aware here, you don ' t has to supply the OS thread ID. The BM command expects the thread number, which is just a zero-based number. When you switch on the correct OS thread with ~~[132e]s, you'll see the thread number in the left bottom part O F WINDBG.

When WINDBG reports a thread number is like, can set a breakpoint with the following command at the functionsqlmin ! Cqueryscan::getrow on the correct thread:

~47 BM sqlmin! Cqueryscan::getrow

After setting the breakpoint, you can continue the execution of sqlservr.exe by using the F5 key. And after a few seconds (depending on the specified delay in the WAITFOR statement) WINDBG should break the execution at t He specific breakpoint:

And now the real fun begins:you can explore the current call-stack with the k command, you can single-step Throu GH The assembly code, seeing how other functions is called. Your choices is endless, and only limited by Your imagination.

Summary

I hope that with today's blog posting I Haven given you a more detailed look into how can successfully set your first Break point within sqlservr.exe to debug a specific query. Over the next weeks and months I ' m trying to blogs more details about how you can troubleshoot SQL Server with WINDBG. Stay tuned for more fun with windbg!

Thanks for Reading

-klaus

Debugging a SQL Server query with WINDBG