Who's online

There are currently 0 users and 20 guests online.

Recent comments

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:

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:

  1. Finding non-default configuration settings in SQL Server
  2. SQL Server