SQL Server THREADPOOL waits: worker thread exhaustion, logins that hang and how to get in with the DAC

#Symptoms

• New connections fail with login timeouts; existing sessions may still work.
• SSMS can't connect; monitoring tools lose contact.
• CPU is not high. The error log may show: New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds (a non-yielding / deadlocked scheduler report).
• Wait statistics show THREADPOOL.

SQL Server has a fixed pool of worker threads. Every running task needs one, including tasks that are only waiting (blocked on a lock, waiting for a parallel query's other threads). When all workers are busy, new tasks, including logins, wait for a worker: THREADPOOL.

#Step 1: get in with the Dedicated Admin Connection (DAC)

The DAC has its own reserved scheduler and worker, so it works when nothing else does.

sqlcmd -S admin:SQLPROD01 -E          # Windows auth, local or remote if enabled
sqlcmd -S SQLPROD01 -A -U sa -P '...' # -A requests the DAC

Remote DAC must be enabled beforehand:

EXEC sp_configure 'remote admin connections', 1; RECONFIGURE;

Only one DAC session is allowed at a time; keep queries light.

#Step 2: confirm worker exhaustion

SELECT max_workers_count FROM sys.dm_os_sys_info;

SELECT scheduler_id, current_tasks_count, runnable_tasks_count,
       current_workers_count, active_workers_count, work_queue_count
FROM   sys.dm_os_schedulers
WHERE  status = 'VISIBLE ONLINE';

SELECT SUM(current_workers_count) AS workers_in_use FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';

work_queue_count > 0 on schedulers and total workers near max_workers_count = exhaustion.

#Step 3: what is holding the workers?

#Blocking chain (most common)

Hundreds of sessions blocked behind one head blocker each hold a worker.

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.command,
       DB_NAME(r.database_id) AS db, LEFT(t.text, 200) AS sql_text
FROM   sys.dm_exec_requests r
CROSS  APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE  r.blocking_session_id <> 0
ORDER  BY r.wait_time DESC;

-- Head blockers: blocking others but not blocked themselves
SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.status, s.open_transaction_count,
       s.last_request_end_time
FROM   sys.dm_exec_sessions s
WHERE  s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
AND    s.session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

A head blocker with status = sleeping and open_transaction_count > 0 is an application that started a transaction and never committed.

#Parallelism storm

Each parallel query uses many workers (up to MAXDOP per parallel branch). Many concurrent parallel queries consume the pool quickly.

SELECT r.session_id, COUNT(*) AS tasks, MAX(r.dop) AS dop
FROM   sys.dm_os_tasks t
JOIN   sys.dm_exec_requests r ON r.session_id = t.session_id
GROUP  BY r.session_id
HAVING COUNT(*) > 8
ORDER  BY tasks DESC;

#Many AG databases

Availability groups use worker threads per database for log capture and redo. Instances with hundreds of AG databases need careful sizing; this shows as a high baseline of busy workers even when quiet.

#Step 4: immediate relief

1. Kill the head blocker (after checking what it is):

KILL 87;

2. If it's a parallelism storm, kill the largest parallel requests or the runaway batch.

Warning

Killing a session rolls back its transaction. A long-running modification can take a long time to roll back, and keeps its locks while it does. Check open_transaction_count and the statement before killing.

#Step 5: lasting fixes

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8; RECONFIGURE;    -- follow Microsoft's MAXDOP guidance for your cores/NUMA

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;   -- per-database override

#About max worker threads

Raising max worker threads treats the symptom. More workers mean more memory (each worker reserves stack memory) and more context switching, and if the cause is a blocking chain, they just fill up again. Leave it at the default (0, automatic) unless Microsoft support advises otherwise for a specific workload such as very many AG databases.

#Monitoring

• Alert on THREADPOOL wait time increasing (sample sys.dm_os_wait_stats every minute).
• Alert on blocking chains older than N seconds (blocked process report: sp_configure 'blocked process threshold (s)' with an Extended Events session).
• Test DAC access during drills, so the on-call engineer knows the command.

#Prevent it next time

• Enable remote DAC on every production instance today.
• Set MAXDOP and cost threshold deliberately.
• Turn on READ_COMMITTED_SNAPSHOT where the application allows it.
• Review application connection pool sizes and transaction scopes.