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.

SQL SERVER - Simple Script to Match Session ID to OS Thread ID sessionidtoosthreadid

Here are few other interesting blog posts which help us to performance tuning with the help of 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)

SQL DMV, SQL Scripts, SQL Server, Windows
Previous Post
SQL SERVER – Authentication in SQL Server (Windows and Mixed Mode) – GDPR Series
Next Post
SQL SERVER – 2017 – Script to Clear Procedure Cache at Database Level

Related Posts

Leave a Reply