LLI Wiki — My Digital Knowledge Base

SQL Server: map session IDs to OS thread IDs

Map each SQL Server session ID (SPID) to the operating system threads that run its tasks.

This query maps each SQL Server session (session_id, or SPID) to the operating system thread IDs (os_thread_id) that run its tasks. It joins the DMVs sys.dm_os_tasks and sys.dm_os_threads on worker_address, so a session seen from SQL Server can be matched with a thread shown by operating system tools.

Query

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
Mastodon LLIMastodon SocialFosstodon