DBA Scripts: Get Worker Threads and Active Sessions

---

There is a particular kind of SQL Server incident where the server is up, CPU looks fine, and yet new connections hang or time out. Applications report login failures, and even your own SSMS connection takes forever. The usual cause is worker thread starvation: SQL Server has run out of threads to assign to incoming requests, and everything queues behind the THREADPOOL wait.

The nasty part is that thread exhaustion hides itself. The server is too starved to answer the very diagnostic queries you would use to diagnose it (the Dedicated Admin Connection exists for exactly this moment). Which is why worker thread usage is worth watching before the lights go out, not after.

This script gives you the one-row summary: the configured maximum worker threads, how many are currently in use, the percentage that represents, and the user session and active request counts alongside.

---

Why Worker Threads Matter

Every request SQL Server executes needs a worker thread, and the pool is finite. The maximum is calculated at startup from the CPU count (for example, 512 base plus additional threads per core beyond 4 on a 64-bit instance), or taken from the max worker threads setting if someone has overridden it. On most servers the pool is nowhere near exhausted, and that is exactly what you want to confirm.

The pool empties fast in two scenarios. The first is massive blocking: hundreds of sessions all stuck behind one lock, each holding a worker thread while it waits. The second is extreme parallelism: a moderate number of queries each consuming many threads. Either way, when current_worker_threads approaches the maximum, new work starts queueing with THREADPOOL waits and the server feels down even though it is running.

• Connection timeouts and login failures while the server looks healthy
• THREADPOOL waits appearing in your wait statistics
• Blocking storms holding threads hostage at scale

---

When to Run This Script

• Routine SQL Server health checks
• When applications report timeouts or login failures but the server is up
• During a blocking storm, to see how close the thread pool is to exhaustion
• When THREADPOOL shows up in wait statistics
• Before and after changing max worker threads or MAXDOP settings

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-WorkerThreadsAndActiveSessions
Category    : performance-troubleshooting
Purpose     : Active user sessions with CPU, elapsed time, and current worker thread pool usage.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-worker-threads-and-active-sessions/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- Worker thread pool usage vs the configured maximum, alongside session/request counts.
-- A pool near max_worker_threads means THREADPOOL starvation is close.
SELECT
    (SELECT max_workers_count FROM sys.dm_os_sys_info)                       AS max_worker_threads,
    SUM(s.current_workers_count)                                            AS current_worker_threads,
    CAST(100.0 * SUM(s.current_workers_count)
         / NULLIF((SELECT max_workers_count FROM sys.dm_os_sys_info), 0)
         AS DECIMAL(5,1))                                                   AS pct_worker_threads_used,
    (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1)   AS user_sessions,
    (SELECT COUNT(*)
     FROM sys.dm_exec_requests r
     JOIN sys.dm_exec_sessions es ON r.session_id = es.session_id
     WHERE es.is_user_process = 1)                                          AS active_user_requests,
    (SELECT ISNULL(SUM(pending_disk_io_count), 0)
     FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE')             AS pending_disk_io
FROM sys.dm_os_schedulers s;

It reads the scheduler DMVs for thread pool state and the session/request DMVs for workload counts, returning a single summary row.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools

.\Initialize-Environment.ps1

# Check worker thread pool usage and session counts:
.\run.ps1 Get-WorkerThreadsAndActiveSessions

# To run against a remote sql server:
.\run.ps1 Get-WorkerThreadsAndActiveSessions -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/performance/active-sessions/Get-WorkerThreadsAndActiveSessions.sql
• powershell/wrappers/performance/active-sessions/Get-WorkerThreadsAndActiveSessions.ps1

---

Example Output

A healthy instance: 93 of 576 worker threads in use (16%), a couple of user sessions, and no pending disk I/O on the schedulers.

---

Understanding the Results

Under 60% used is normal operation, even on busy servers. SQL Server parks idle workers, so a steady baseline well below the maximum is what healthy looks like.

Over 80% used deserves attention right away. Cross-check active_user_requests against user_sessions: a big gap between threads in use and requests running usually means blocked sessions are holding threads while they wait.

At or near 100%, you are in THREADPOOL territory and new connections will hang. If you cannot even connect to run this script, use the Dedicated Admin Connection (ADMIN:servername), which reserves its own scheduler and thread for exactly this situation.

One nuance: a high thread count with a low request count is almost always blocking, not workload. Killing the head blocker releases hundreds of threads at once. Raising max worker threads in that situation just gives the blocking storm more threads to consume.

---

Best Practices

• Leave max worker threads at 0 (automatic) unless you have a measured reason. The calculated default is right for almost every server, and raising it to “fix” starvation usually masks a blocking or parallelism problem.
• Baseline your normal. Capture this output with your regular health checks so you know what your instance idles at; the trend to the ceiling matters more than any single reading.
• Chase the cause, not the number. When the percentage climbs, the next step is your blocking and active session scripts, because threads are consumed by waiting just as much as by working.

---

Related Scripts

You may also find these scripts useful:

• Query and Performance Tuning (hub)
• Active Requests
• Active Requests with Plan
• Active Sessions
• Long Running Queries
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Worker thread starvation is one of the few problems that can take a healthy-looking SQL Server offline for practical purposes while every dashboard stays green. The defence is knowing your baseline, and this one-row summary makes that cheap: maximum, in use, percentage, and the session counts to interpret them.

I keep this in the routine health-check rotation and reach for it the moment “the server is up but nobody can connect” gets reported. If the percentage is high and requests are low, go find the head blocker before touching any configuration.