Breakpointing SQL Execution with WinDbg

Unlike Oracle, SQL Server has just one server process whose threads handle all of the backround tasks and user requests. Consequently, if you attach to it with WinDbg and set a breakpoint, the program will suspend its execution the first time a thread comes to the breakpoint. But usually, you don’t won’t to stop when a random thread reaches the breakpoint. Instead, what you’re interested in is, to pause the execution only when some specific thread, namely the one which is executing a SQL of interest, reaches it. In this blog post I’ll be explaining how to do that.

First of all, we need to identify the id of the OS thread which is executing our SQL. Fortunately, the layers participating in SQL execution scheduling are exposed through the dynamic views. Firstly, we can get the OS thread information from the dynamic view sys.dm_os_threads. Secondly, the worker thread, which is the OS thread’s logical representation, is externalized through the dynamic view sys.dm_os_workers. Finally, a SQL request gets assigned to a worker as a task and can be examined by querying sys.dm_exec_requests. Each of these layers contains pointers to the structures in the layer below.

Having said that, it’s easy to follow the cascade described above to find out the OS thread id:

select convert(VARBINARY(8),t.os_thread_id)
  from sys.dm_exec_requests r join sys.dm_os_workers w 
    on r.task_address = w.task_address  
  join sys.dm_os_threads t 
    on w.worker_address = t.worker_address
  where session_id=67 ;
  
0x00000ACC

In our example, the SQL of interest is running in the session 67.

Next, we’ll use this thread_id to identify the thread in WinDbg. We’ll do that by listing all the threads with the ~ command and then looking for our OS thread id:

~
...
  84  Id: 1b2c.460 Suspend: 1 Teb: 00000033`3ffe9000 Unfrozen
  85  Id: 1b2c.2654 Suspend: 1 Teb: 00000033`3ffeb000 Unfrozen
  86  Id: 1b2c.acc Suspend: 1 Teb: 00000033`3ffed000 Unfrozen
  87  Id: 1b2c.2af8 Suspend: 1 Teb: 00000033`3ffef000 Unfrozen
  88  Id: 1b2c.2af0 Suspend: 1 Teb: 00000033`3fff1000 Unfrozen
  89  Id: 1b2c.2950 Suspend: 1 Teb: 00000033`3fff3000 Unfrozen
  90  Id: 1b2c.2398 Suspend: 1 Teb: 00000033`3fff5000 Unfrozen
...

As you can see, our thread of interest is the thread 86.

Now we can set the breakpoint for this particular thread:

~86 bm sqlmin!BTreeMgr::GetHPageIdWithKey

The program, indeed, stopped when the thread 86 hit the breakpoint:

0:086> g
Breakpoint 1 hit
sqlmin!BTreeMgr::GetHPageIdWithKey:
00007ffe`d4b300a0 4c89442418

References:

Joseph Pilov: Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?