• Blog
  • Get Help Now!
  • Customer Portal

BlueGecko Denmark

  • Why you need us
    • Why act now
    • Why are we unique
  • What we do
    • Products Supported 24/7
      • MySQL
      • Postgres
      • SQL Server
      • Open Source
      • OS & Storage
  • How we do it
    • Consulting
    • System Monitoring
    • Security
  • Support
    • Blog
      • Oracle
      • MySQL
      • News & Events
      • Oracle Applications
      • Amazon Web Services
      • Jeremiah Wilton’s Oradeblog
      • OurSQL Community Podcast
      • Remote DBA
    • Team viewer
    • Whitepapers
  • About Us
    • Philosophy
    • The Blue Gecko Teams
    • Employment Opportunities
    • Partners
    • News
    • Press
  • Contact Us

August 18, 2010 by jwilton

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

Filed Under: Jeremiah Wilton's Oradeblog, SQL Server

Categories

  • Amazon Web Services
  • Configuration Management
  • downtime
  • Drizzle
  • ebs
  • Education
  • elastic block store
  • elastic compute cloud
  • hosting
  • hot backup
  • ignorance
  • Infrastructure
  • IOUG
  • Jeremiah Wilton's Oradeblog
  • misconception
  • misconceptions
  • Monocle
  • MySQL
  • MySQL Council
  • News
  • News & Events
  • Oracle
  • Oracle APEX
  • Oracle Applications
  • Oracle Data Guard
  • Oracle Database
  • Oracle Database Appliance
  • Oracle Linux
  • Our People
  • OurSQL Community Podcast
  • outage
  • parallel
  • performance
  • podcast
  • proof
  • Remote DBA
  • replication
  • S3
  • Security
  • SQL Server
  • System monitoring
  • Tomcat
  • Uncategorized

Contact Blue Gecko

  • This field is for validation purposes and should be left unchanged.

Get Our Newsletter

  • This field is for validation purposes and should be left unchanged.

The Fine Print

  • Privacy Policy
  • Terms of Use
  • When is remote DBA the right solution
  • What are remote DBA services
  • Get Help

Blog Categories

  • 24/7 support
  • Blue Gecko 360° Services
  • Products Supported 24/7

Virtual runs

Contact Blue Gecko

Blue Gecko A/S
Slotsgade 21
DK – 4200 Slagelse
Denmark
Phone: (DK) +45 70 60 51 20

Networks

  • Email
  • Twitter

© Blue Gecko Group