SQL SERVER – Simple Script to Match Session ID to OS Thread ID

I often get asked a question about how actually Session ID of SQL Server is mapped to Operating System’s’ Thread ID. Well, the answer is very simple, here is the script which provides us three important details – SQL Session ID, OS Thread ID and OS Scheduler ID along with the state of the task.

Here is the quick script which lists all of them –

SELECT osTask.session_id, osThreads.os_thread_id,
osTask.scheduler_id, osTask.task_state
FROM sys.dm_os_tasks AS osTask
INNER JOIN sys.dm_os_threads AS osThreads
ON osTask.worker_address = osThreads.worker_address
WHERE osTask.session_id IS NOT NULL
ORDER BY osTask.session_id;
GO 

Once you know the OS Thread ID, you can further take your investigation with Windows Performance Monitor commonly known as PerfMon. Please note that the script here will not list any sleeping thread but will list all the rest of the threads.

Here are few other interesting blog posts which help us to performance tuning with the help of SPID:

• SQL SERVER – Get Wait Stats Related to Specific Session ID With sys.dm_exec_session_wait_stats
• SQL SERVER – Get Query Running in Session
• How to Identify Session Used by SQL Server Management Studio? – Interview Question of the Week #151
• SQL SERVER – Get Last Running Query Based on SPID

Let me know if you ever used such script to do performance tuning diagnosis. I would love to hear your ideas. Please leave a comment and I will publish your comment on the blog post with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)