Nobody Chose 704, and That’s Why SQL Alerts Wouldn’t Stop

Sep 21

10 min read

A monitoring probe kept firing “SQL Connection Error — Disaster” and clearing itself sixty seconds later. The engine never went down. It was never CPU-starved. It simply had no worker left to hand the next login. This is how I proved that from the DMVs, why max worker threads was the right lever in this one specific case, and the single check that tells you whether it is the right lever for you — because most of the time it isn’t.

“Raise max worker threads” is the kind of advice that shows up in forum answers and makes experienced DBAs wince, because nine times out of ten it masks a problem instead of fixing one. I raised it anyway, on a production OLTP primary, and it stopped the alert flapping the same day. The difference between those two outcomes is one query. All identifiers below are changed; the numbers are real.

The symptom: a Disaster alert that resolves itself

The page said the database server was unreachable. By the time anyone opened a session, it was fine. That pattern repeated for weeks:

Monitoring fires SQL Connection Error [Disaster] on the availability-group primary.
It self-clears in about one minute, on the next poll.
Separately, an application dashboard reports failed database calls to one hot stored procedure in the same window.

Two alerts, two teams, and a very natural — and wrong — first conclusion: the network blipped, or the monitor is noisy.

First, rule out the boring catastrophes

Before theorising, eliminate the things that would make everything else irrelevant. Four checks, five minutes:

Check	Result	Conclusion
Memory dumps / stack dumps	0	No access violation, no crash
AG role & health	Same primary throughout, HEALTHY / SYNCHRONIZED	No failover
sp_server_diagnostics	Answering continuously across the flap	The engine never stopped responding
Error log around the flap	No errors at the flap minute	Nothing was logged as broken

That last row is the interesting one. The engine was healthy and answering its own internal health check at the exact second the monitor declared it unreachable. So the engine was up — it just wasn’t accepting new connections. Those are different failures, and they have different DMVs.

Useful distinction: “the database is down” and “the database would not accept my login” look identical from a monitoring probe, which is itself just another login. If your health check is a connection attempt, it cannot tell those two apart. Read the connectivity ring buffer before you believe the word “down.”

Aside: why “five minutes” is not a boast

Worth naming the tooling, because it changed the shape of this investigation. I built an MCP server over our SQL estate — a read-only diagnostic layer my editor can call directly. Every check in this post was a tool call, not a remote-desktop session, a jump host, and a folder of saved .sql files.

Three properties mattered specifically for this failure:

Historical capture beats live debugging for a flap. A Disaster alert that clears in sixty seconds is almost impossible to catch interactively — by the time you are connected, the evidence is gone. Because blocking snapshots are captured to a history table roughly once a minute, I could query the incident window after it closed and read the exact wait type, duration and statement text at the flap second. Without that, this investigation is a shrug and a “let us watch it next time.”
Fleet-wide fan-out in one call. Running the same scheduler and configuration query across every node and region at once answers “is this one box or all of them?” immediately — and it is how I confirmed the 704 ceiling was inherited everywhere, not a local misconfiguration.
Read-only by construction. The transport permits SELECT only; writes and DDL are rejected at the tool layer. That is what makes it acceptable to run diagnostics against a production primary during an incident rather than asking for a change window to investigate.

The gain isn’t that an AI answered the question. It is that the cost of asking a question fell from minutes to seconds — so I asked twenty instead of three, and stopped guessing. Most wrong root causes I have shipped came from a hypothesis that was expensive to check, so nobody checked it.

The ring buffer: the logins never got a worker

SQL Server records rejected and aborted connections in a ring buffer that almost nobody reads. It is the fastest way to turn “connection error” into a specific cause:

SELECT CAST(record AS xml) AS rec, timestamp
FROM   sys.dm_os_ring_buffers
WHERE  ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
ORDER  BY timestamp DESC;

The result was unambiguous. 1,019 of the 1,024 retained records were SNI error 17830 — a login timeout — spanning a continuous 16-minute window that straddled the flap. And the detail that settles it:

Most of those records carried spid = 0. The connection never got a session id at all. It did not connect and then fail; it was never given a worker to be connected with.

So the monitoring probe — a five-second login attempt — was one of a thousand logins queuing for a worker that never arrived. It timed out, raised PROBLEM, and on the next poll a worker was free, so it raised OK. That is the entire flap.

THREADPOOL: a 42-second wait for a thread

THREADPOOL is the wait a task registers when it is ready to run but there is no worker thread to run it on. In a healthy instance it is zero or trivial:

SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM   sys.dm_os_wait_stats
WHERE  wait_type = 'THREADPOOL';

Maximum single wait: 42.5 seconds.

That number alone explains the SNI 17830s. A client login timeout is typically 15 or 30 seconds. If a task can wait 42 seconds for a worker, then logins arriving during that burst are mathematically guaranteed to time out before they are ever serviced. The login timeout wasn’t a symptom to be correlated — it was the arithmetic.

The counterintuitive reading: 823 workers against a ceiling of 704

Now the scheduler view, which is where the decision actually gets made:

SELECT SUM(current_workers_count) AS workers_in_use,
       SUM(active_workers_count)  AS active,
       SUM(runnable_tasks_count)  AS runnable,      -- queued for CPU
       SUM(work_queue_count)      AS queued_no_worker
FROM   sys.dm_os_schedulers
WHERE  scheduler_id < 255;

SELECT max_workers_count, cpu_count FROM sys.dm_os_sys_info;

Metric	Value during the storm	Meaning
max_workers_count	704 (auto)	The computed ceiling on 16 logical CPUs
current_workers_count	823	Already over the nominal ceiling
work_queue_count	28–59	Tasks with no worker at all
runnable_tasks_count	~24	Tasks queued for CPU — low

Two things worth knowing here. First, max worker threads = 0 does not mean unlimited — it means auto-computed, and on a 64-bit box the formula is 512 + (logical_CPUs − 4) × 16. On 16 CPUs that is 704. Most people who believe they have no worker limit have 704.

Second, and this is the whole post: workers in use (823) was high, but tasks queued for CPU (~24) was low. Those two facts together say something very specific.

The one check that decides it

Worker starvation has two completely different causes, and they demand opposite responses.

	CPU-bound starvation	I/O- or latch-bound starvation
Signature	runnable_tasks_count high; workers burning CPU	runnable_tasks_count low; workers parked on waits
Dominant waits	SOS_SCHEDULER_YIELD, CPU at 100%	PAGEIOLATCH_, PAGELATCH_, blocking
Raising max worker threads	Makes it worse — more threads fighting for the same saturated CPU, more context switching	Helps — the new workers mostly sleep on I/O; CPU is free to service the login path

So: what were the 823 workers actually doing?

SELECT wait_type, COUNT(*) AS sessions
FROM   sys.dm_exec_requests
WHERE  session_id > 50 AND wait_type IS NOT NULL
GROUP  BY wait_type
ORDER  BY sessions DESC;

They were asleep. Blocked sessions ran 150–464 deep, with PAGEIOLATCH peaking at 469 and PAGELATCH at 419 — individual single-page reads taking 744 ms. CPU was not the constraint; the storage volume was. The workers weren’t competing for processor, they were parked, each one holding a thread hostage while it waited on a disk read.

This is the check. Before you touch max worker threads, run the scheduler query and ask one question: are my workers running, or waiting? High runnable_tasks_count means running — do not add workers, you will make it worse. Low runnable_tasks_count with deep PAGEIOLATCH/PAGELATCH means waiting — adding workers is safe and will stop the login rejections.

What was actually parking 800 workers

Worth stating plainly, because the worker bump treats the symptom and you need to know what the disease is. Three compounding causes:

A stored procedure scanning a 31-day partition range to update one row. The status-update procedure ran WHERE CreatedDate BETWEEN DATEADD(DAY,-31,...) AND DATEADD(DAY,1,...) against a table partitioned by day. That predicate spans roughly 32 daily partitions on every single call — to touch one row.
Query Store showed ~75% of those calls changed nothing. Average rows updated per call: 0.245. The no-op calls fell through to an IF EXISTS fallback that repeated the same 31-day scan — roughly 1.8 billion logical reads a day of pure no-op scanning. That is where most of the latching came from.
All data files on one volume, and a cache far too small. Every data file of a 3.28 TB database sat on a single volume with write latency of 140–231 ms (over 100 ms is “critical”). With 126 GB of RAM only ~57 GB was cached, and page life expectancy during the storm was 54 seconds. Day-partitioning bought retention, not I/O parallelism.

Chain: traffic burst → thousands of 32-partition scans → cold reads swamp one volume → sessions park on PAGEIOLATCH → their workers are pinned → new logins find no worker → SNI 17830 → the probe times out → Disaster. It self-clears because the burst subsides.

The fix: online, reversible, no restart

max worker threads is a dynamic setting. It takes effect on RECONFIGURE — there is no service restart, which is what makes it viable as a same-day mitigation on a production primary.

-- 0) Baseline BEFORE changing anything. Record these.
SELECT max_workers_count FROM sys.dm_os_sys_info;                 -- 704 (auto)
SELECT SUM(current_workers_count) AS cur,
       SUM(work_queue_count)      AS queued
FROM   sys.dm_os_schedulers WHERE scheduler_id < 255;              -- 823 / 59

-- 1) Enable advanced options
EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE;

-- 2) Confirm the current value (0 = auto = 704 here, NOT unlimited)
EXEC sys.sp_configure 'max worker threads';

-- 3) Set an explicit ceiling -- dynamic, applied by RECONFIGURE
EXEC sys.sp_configure 'max worker threads', 1024;
RECONFIGURE;

-- 4) Verify it took effect
EXEC sys.sp_configure 'max worker threads';        -- run_value = 1024
SELECT max_workers_count FROM sys.dm_os_sys_info;  -- now reads 1024

-- 5) Optional: hide advanced options again
EXEC sys.sp_configure 'show advanced options', 0; RECONFIGURE;

The memory arithmetic, because that is the real objection

Each potential worker reserves thread stack — about 2 MB on x64 — outside the buffer pool, and it is reserved address space, only committed when the thread is actually created. Going 704 → 1024 is 320 extra workers:

320 workers x 2 MB = ~640 MB of reserved VAS
on a 126 GB server = ~0.5% of RAM, none of it taken from the buffer pool

That is the entire cost. It is why this is a cheap thing to try once you have proven the I/O-bound signature — and why it is still a bad idea when you haven’t, since on a CPU-bound box you pay scheduling overhead to make contention worse.

Three operational details that are easy to miss

Apply it to every replica, not just the primary. The setting is instance-level. The secondary becomes primary on failover — if you only fix one node you have armed a surprise for your next failover.
Do not set it to 32767. Enormous values waste address space, add scheduler overhead, and hide the real problem. For a 16-vCPU box, 1024 is a sensible first step and ~2048 a reasonable ceiling if flaps persist.
It is reversible. Set it back to 0 through the same steps to return to auto. Knowing the rollback is one statement is what makes this safe to do during business hours.

Verifying it actually worked

Do not declare victory because the alert went quiet for an hour — this failure mode is bursty and self-clearing, so quiet proves nothing. Check the two measurements that caused the alert, during the next comparable traffic peak:

-- 1) Did the login rejections stop? Expect SNI 17830 to fall away.
SELECT CAST(record AS xml) AS rec, timestamp
FROM   sys.dm_os_ring_buffers
WHERE  ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
ORDER  BY timestamp DESC;

-- 2) Are tasks still going unserviced? Expect work_queue_count at/near 0.
SELECT SUM(current_workers_count) AS cur,
       SUM(work_queue_count)      AS queued,
       SUM(runnable_tasks_count)  AS runnable
FROM   sys.dm_os_schedulers WHERE scheduler_id < 255;

A successful outcome looks like this: work_queue_count sits near zero, the ring buffer stops filling with 17830, and the connection-error alert stops firing — while the underlying storage stalls are still happening. That last clause is not a caveat, it is the point. You have decoupled the monitoring flap from the storm. The storm is still there.

Be honest about what this does not fix

This is a mitigation, and saying otherwise is how a mitigation quietly becomes the permanent answer.

Still broken after the change	What it actually needs
Single-page reads taking 744 ms	More IOPS/throughput on the data volume; longer term, spread the filegroups across multiple volumes for real parallel I/O
~1.8 billion no-op logical reads/day	Rewrite the procedure: a short fast-path range first, then an existence-gated tail for the rare old row
Page life expectancy of 54 seconds	More RAM — a multi-terabyte hot table with a ~57 GB cache will always read cold
Application command timeouts	Unchanged — the worker bump fixes the login path, not query duration

One design note from the procedure rewrite, because it is a trap worth naming: when you split a wide range into a fast path plus a fallback, gate the fallback on existence, not on @@ROWCOUNT = 0. With ~75% of calls legitimately updating zero rows, branching on rowcount sends three-quarters of all traffic straight into the expensive tail — converting a slow path into the hot path and ending up worse than where you started.

What to copy

A connection-based health check cannot distinguish “down” from “would not accept my login.” When a probe flaps and self-clears, read RING_BUFFER_CONNECTIVITY before accepting the word “down.”
spid = 0 on a login timeout means the connection never got a worker. That single field turns a vague network suspicion into a specific, provable engine condition.
max worker threads = 0 is not unlimited. It is 512 + (logical_CPUs − 4) × 16. Check sys.dm_os_sys_info.max_workers_count and know your real number before you assume you have headroom.
Compare runnable_tasks_count against work_queue_count. Low runnable + high queued + deep PAGEIOLATCH = workers parked on I/O, and raising the ceiling is safe. High runnable = CPU-bound, and raising it makes things worse. This is the entire decision.
Compare THREADPOOL max wait against your client login timeout. If the wait exceeds the timeout, login failures are arithmetic, not coincidence — stop hunting for a correlation.
Do the memory arithmetic out loud (~2 MB reserved per worker, outside the buffer pool) so the change is a calculation rather than a gamble.
Apply instance-level settings to every replica. The secondary is a future primary.
Verify against the causal metric, not the alert. A self-clearing alert going quiet proves nothing; work_queue_count near zero during peak proves something.
Write down what the mitigation does not fix, in the same document, on the same day. Otherwise the ticket closes and the 744 ms reads live forever.
Capture blocking history on a schedule — you cannot debug a sixty-second flap live. Snapshot waits and statement text to a table every minute, and make the diagnostics cheap to run (I put mine behind a read-only MCP server). The investigation is only fast because the evidence was already on disk before anyone was paged.

The lesson isn’t “raise your worker threads.” It is that the same symptom — tasks with no thread — has two opposite causes, and one query separates them. I spent the first pass of this investigation assuming a noisy monitor, and the second assuming CPU pressure. The DMVs disagreed with both in about ten minutes — because asking them had been made cheap enough that I bothered to ask. Build that, and the DMVs will disagree with your assumptions too, long before you have committed to one.