SQL SERVER – Map SQL Server Session ID to OS Thread ID

During the recent Comprehensive Database Performance Health Check, I had a very interesting conversation with the IT Admin of my client. IT Admin was very familiar with the Windows environment and wanted to track the SQL Session ID as OS Thread ID in Performance Monitor. He asked me if I know any script which can Map SQL Server Session ID to OS Thread ID.

Of course, there is a script for it. Here is it.

SELECT ost.session_id, osth.os_thread_id  
FROM sys.dm_os_tasks AS ost  
INNER JOIN sys.dm_os_threads AS osth  
	ON ost.worker_address = osth.worker_address  
WHERE ost.session_id IS NOT NULL  
ORDER BY ost.session_id;  
GO

Now you can run the above script and get the mapping of the SQL Session ID with OS Thread ID. You can also open Performance Monitor from your Windows OS and also track the behavior of the ID.

Well, that’s it for today.

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

Reference: Pinal Dave (http://blog.SQLAuthority.com)