Skip to main content

Find SQL Background Thread Id's and Names.

-- ----------------------------------------------------------------------------
-- Finding Thread Ids And Names Of Sql Server Background Threads
--
-- 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.
--
-- http://www.bluegecko.net/sql-server/finding-thread-ids-and-names-of-sql-server-background-threads/
-- ----------------------------------------------------------------------------

select session_id,
    command,
    os_thread_id
from sys.dm_exec_requests as r
join sys.dm_os_workers as w
    on r.task_address = w.task_address
join sys.dm_os_threads as t
    on t.thread_address = w.thread_address
where session_id <= 50
order by session_id;