In Microsoft SQL Server, session IDs of 50 or less are dedicated background threads. These are analogous to Oracle’s background processes. Suppose you needed to obtain the Windows thread ID of a SQL server background thread. Just try finding out how to do that in the Microsoft documentation or by Googling. I tried, and came up empty-handed.
Why might you need to know this kind of thing? Well suppose one of those threads was misbehaving or hanging. You would need to debug it and obtain a stack trace. For that you would need the OS thread ID.
A little sleuthing shows that you need to query three dynamic system views: sys.dm_os_threads, sys.dm_os_workers and sys.dm_exec_requests. The following SQL should do the trick in MSSS 2005 and 2008:
1> select session_id, command, os_thread_id 2> from sys.dm_exec_requests as r 3> join sys.dm_os_workers as w on r.task_address = w.task_address 4> join sys.dm_os_threads as t on t.thread_address = w.thread_address 5> where session_id <= 50 6> order by session_id 7> go
session_id command os_thread_id ---------- ---------------- ------------ 1 RESOURCE MONITOR 2776 2 XE TIMER 2908 3 XE DISPATCHER 2416 4 LAZY WRITER 1316 5 LOG WRITER 2404 6 LOCK MONITOR 1592 7 SIGNAL HANDLER 1516 9 TRACE QUEUE TASK 1544 10 BRKR TASK 1540 11 TASK MANAGER 1524 13 CHECKPOINT 1520 14 BRKR EVENT HNDLR 1556 15 BRKR TASK 1552 16 BRKR TASK 1536
With these IDs in hand, you can use a thread debugging tool like Sysinternals Process Explorer to kill, debug or otherwise troubleshoot the problem.
Related posts: