sys.dm_os_threads – Day 15 – One DMV a Day

Hello Geeks and welcome to the Day 15 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have covered sys.dm_os_workers and a bit more insight into scheduling. Today I will be talking about one more important DMV related to OS, sys.dm_os_threads. This DMV helps us correlate the SQL Server workers to OS threads.

Sys.dm_os_threads exposes a lot of information related to windows threads. While most of it is related to windows level information few columns helps us join to other DMVs. Every task to do some work needs a worker. A worker gets a scheduler which is associated to a CPU at windows level. The worker is associated to a thread.

Today I will be walking you through a demo using windows debugger, yes you have heard it right. I will be showing you how to see a stack of a thread in debugger. DO NOT RUN IT ON A PRODUCTION OR ANY OTHER MACHINES IN YOUR BUSINESS SETUP or you may lose your job. Use lab machine or your local machine to experiment with this. Before that let us see how a thread is started and used by SQL OS.

In a simple program you would initiate a thread like this.

//Main Thread:
CreateRemoteThreadEx (…, readNextDMVBlog, (void *)data)
…
//Thread 1:
void *readNextDMVBlog (void *data)
…

SQL OS executes a thread as below. Yes, this is how the code looks like and this can be seen from the debugger stack calls using public symbols. Read down to believe me.

//System dispatcher or another worker thread:
CreateRemoteThreadEx(…, SchedulerManager::ThreadEntryPoint, <ptr to SchedulerManager>)
…
//Thread 1:
void * SchedulerManager::ThreadEntryPoint (void *)
{
	// Get Worker
	// Run Worker
	// Get Task
	// Run Task
	// GOTO “Get Task”
}

Let us connect to SQL Server and run the below query to get the os_thread_id associated with any task. I will be showing LazyWriter as it comes up in the top of the list most of the times. Including sys.dm_os_threads I will be using five DMVs in this query. I will get the associated details for each worker, their tasks, schedulers, the command running and the thread id.

SELECT s.scheduler_id,  s.status, w.worker_address, w.is_preemptive, r.command, r.status, th.os_thread_id
FROM sys.dm_os_workers w
JOIN sys.dm_os_schedulers s
ON w.scheduler_address = s.scheduler_address
LEFT OUTER JOIN sys.dm_os_tasks t
ON t.task_address = w.task_address
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = t.session_id
LEFT OUTER JOIN sys.dm_os_threads th
ON th.thread_address = w.thread_address
GROUP BY s.scheduler_id, s.status, w.worker_address, w.is_preemptive, r.command, r.status, th.os_thread_id, th.started_by_sqlservr
ORDER BY s.scheduler_id
GO

From the output of sys.dm_os_threads we can see the OS thread id associated with LazyWriter is 10932. Convert this decimal value to hexadecimal and it equals 2AB4.

Now open windows debugger and attach it to the sqlservr.exe process. Follow this KB from Microsoft to setup windbg and load the public symbols.

After you attach the SQL Server process it is a simple command to see the stack of the thread. The command you should run is ~~[2AB4]k. The “k” will display the stack of the thread.

Remember to detach the process before you close the debugger. It will result in SQL Service restart if you fail to detach.

I have shown you how to use sys.dm_os_threads to see a specific thread in debugger. You can relate the stack with the thread code from the earlier part of the post. The thread gets the worker, its task and executes the task. You can also observe that the LazyWriter is called using SQLMIN module. I will talk about that in tomorrow’s blog. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook