When More CPU Didn't Help: Tracing SQL Server THREADPOOL Starvation to a Single SET Option

A production Microsoft SQL Server would periodically stop responding.

There was no clear schedule and no obvious workload pattern. Between incidents, the same database could run normally, which made post-incident troubleshooting largely inconclusive.

Before we became involved, the client’s team had already tried the most obvious mitigation: increasing server capacity. More memory had been allocated and additional CPU cores had been added.

The outages continued.

We did not continue scaling the server. The fact that additional capacity had failed to change the behavior was useful evidence in itself. We started capturing what SQL Server was doing during an incident and followed the waits and blocking chain instead.

The root cause eventually came down to one unnecessary line inside a frequently executed stored procedure:

1

SET ANSI_WARNINGS OFF;

Removing it stopped the incidents.

The code change was trivial. Finding it was not.

The problem

From the application side, the failure looked like a heavily overloaded database.

During an incident:

• new queries became extremely slow or stopped progressing;
• active and waiting requests accumulated rapidly;
• eventually more than a thousand sessions could be involved;
• the database became effectively unavailable;
• once the incident cleared, SQL Server returned to apparently normal operation.

Scaling the server may sound like the most reasonable response to this kind of symptom. If a database appears saturated, adding CPU or memory is an easy hypothesis to test.

In this case it had already been tested, before our investigation began, and it had not solved the problem.

That shifted the question from capacity to contention:

What was preventing requests from completing long enough for the server to run out of workers?

The first useful clue: THREADPOOL

The initial analysis showed THREADPOOL waits.

1

THREADPOOL

SQL Server reports THREADPOOL when a task is waiting for a worker thread. Microsoft notes that this most commonly happens when executions take unusually long and reduce the number of workers available for other work.¹

So THREADPOOL explained why the server eventually became unresponsive, but not what started the incident.

The visible failure looked roughly like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

requests stop completing
        ↓
active requests accumulate
        ↓
workers remain occupied
        ↓
available workers are exhausted
        ↓
THREADPOOL
        ↓
new work cannot progress

Changing max worker threads at this point would have treated the final stage of the failure rather than explained why so many workers were occupied.

Microsoft also describes max worker threads as an advanced option and recommends leaving SQL Server’s automatic configuration in place for most systems.²

We needed to go further upstream.

  flowchart TD
    A["SQL Server periodically becomes unresponsive"] --> B["Client team increases CPU and memory"]
    B --> C["Incidents continue"]

    C --> D["Techpipe investigation starts"]
    D --> E["Capture requests, waits and blocking during an incident"]
    E --> F["THREADPOOL detected"]
    F --> G["Find why workers remain occupied"]
    G --> H["Follow the blocking chain"]
    H --> I["Blocked resource points to a stored procedure"]
    I --> J["Investigate compile contention"]

Capturing the failure while it was happening

The incident was intermittent, so a normal performance snapshot taken ten minutes later was of limited value.

We added monitoring around the failure state and captured:

• active sessions and requests;
• request duration and status;
• current waits;
• blocking relationships;
• blocked resources.

Once an incident was captured, the scale of the pile-up became clear. A large number of sessions were waiting, but the blocking chain did not initially look like a typical SQL Server locking problem.

There was no obvious transaction sitting on a row or table lock for several minutes.

More confusingly, the session at the head of the blocking chain could appear without a useful wait type:

1
2

wait_type = NULL
status    = runnable

Looking only at the wait-type column did not tell us what that session needed.

The blocked resource did.

Following the resource instead of the wait type

The blocking information repeatedly pointed to the same object.

It was not a table or index. It was a stored procedure.

That changed the direction of the investigation.

SQL Server has a specific blocking pattern around stored procedure compilation. Microsoft documents cases where one session obtains an exclusive compile lock while other sessions trying to execute the same procedure wait behind it. The current blocker can show waittype = NULL, and the role of head blocker can move from one session to another as each compilation proceeds.³

The wait resource in this type of incident can identify the affected object as a compile resource:

1

OBJECT: <database_id>:<object_id> [[COMPILE]]

That matched the behavior we were seeing much better than the original assumption of general resource pressure.

The problem was now narrower:

Why were concurrent executions of this particular stored procedure repeatedly getting involved in compilation?

When compilation becomes a concurrency problem

Compiling a stored procedure is normally not a problem by itself.

SQL Server can compile a procedure, cache its execution plan, and reuse that plan for later executions. Under normal conditions, hundreds or thousands of calls do not imply hundreds or thousands of compilations.

The situation changes if the statement keeps requiring recompilation.

With enough concurrent callers, compilation can become a serialization point. Microsoft describes how compile locks can cause many sessions executing the same stored procedure to block while an exclusive compile lock is held.³

That gives a very different failure mode from one simply expensive query.

The individual procedure call does not have to consume extreme amounts of CPU.

Enough callers queueing behind the same point are sufficient.

In our case the failure path was becoming clearer:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

frequently executed stored procedure
        ↓
repeated recompilation
        ↓
compile-lock contention
        ↓
concurrent requests accumulate
        ↓
workers remain occupied
        ↓
THREADPOOL

We now understood the mechanism.

We still did not know why the procedure was recompiling.

  flowchart TD
    A["Many concurrent calls"] --> B["Frequently executed stored procedure"]
    B --> C["SET ANSI_WARNINGS OFF"]
    C --> D["SET option changes execution context"]
    D --> E["Statement recompilation"]
    E --> F["Compile-lock contention"]
    F --> G["Concurrent executions queue"]
    G --> H["Blocking chain grows"]
    H --> I["Workers remain occupied"]
    I --> J["Worker pool exhaustion"]
    J --> K["THREADPOOL"]
    K --> L["SQL Server becomes unresponsive"]

    C --> M["Remove unnecessary SET statement"]
    M --> N["Recompilation pattern disappears"]
    N --> O["Compile contention stops"]
    O --> P["Incidents stop"]

Extended Events identified the recompilation cause

This was where Extended Events provided the missing piece.

SQL Server exposes the event:

1

sql_statement_recompile

for statement-level recompilation.

More importantly for this investigation, the event includes recompile_cause. Microsoft documents a number of possible values, including schema and statistics changes, deferred compilation, temporary-table changes, explicit OPTION (RECOMPILE), and ⁴:

1

SET option changed

That was the reason captured for the affected procedure.

This eliminated several plausible paths of investigation at once. We were no longer looking for a schema deployment, statistics churn, plan-cache pressure, or an explicit recompile hint.

We started looking at the procedure’s execution context.

The root cause was one SET statement

The procedure contained:

1

SET ANSI_WARNINGS OFF;

ANSI_WARNINGS is a session-level SET option. Microsoft documents that its value is applied at execution time and affects the current session.⁵

The Extended Events trace showed recompilation with:

1

SET option changed

and inspection of the procedure led back to the ANSI_WARNINGS change.

At that point we checked whether the procedure actually depended on ANSI_WARNINGS OFF.

It did not.

The statement was leftover code and could be removed without changing the required procedure logic.

Before

1
2
3
4
5
6
7
8

CREATE PROCEDURE dbo.ProcessSomething
AS
BEGIN
    SET ANSI_WARNINGS OFF;

    -- procedure logic
    ...
END;

After

1
2
3
4
5
6

CREATE PROCEDURE dbo.ProcessSomething
AS
BEGIN
    -- procedure logic
    ...
END;

The actual production change was essentially:

1

- SET ANSI_WARNINGS OFF;

There was no need to increase max worker threads, add more CPU, or redesign the procedure.

Result

After removing the unnecessary SET statement, the observed recompilation pattern stopped.

The compile contention disappeared with it. The large blocking chains stopped forming, worker starvation no longer developed, and the periodic SQL Server hangs did not return.

A server that had already been given more CPU and RAM was fixed by deleting one line of T-SQL.

The useful part of the case was not the size of the fix. It was the path used to find it.

The actual failure chain

Once all the evidence was available, the incident could be reconstructed from cause to symptom:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SET ANSI_WARNINGS OFF
        ↓
SET execution context changes
        ↓
statement recompilation
        ↓
compile-lock contention
        ↓
concurrent executions queue
        ↓
large blocking chain
        ↓
workers remain occupied
        ↓
worker pool exhaustion
        ↓
THREADPOOL
        ↓
SQL Server becomes effectively unavailable

CPU and memory pressure appeared near the end of this chain.

That is why the earlier resource increases did not remove the failure condition. They could change how much load the server tolerated before reaching the same state, but they did not remove the serialization point that created the queue.

Why this problem was easy to misdiagnose

Several things made the incident look more conventional than it was.

It was intermittent

Most of the useful evidence existed only while the database was failing. Once the blocking cleared, ordinary server metrics looked much less interesting.

THREADPOOL was real, but it was downstream

SQL Server really was short of available workers.

That observation was correct. It just was not the root cause.

The head blocker did not look obviously blocked

A blocker with an empty wait type does not immediately point toward compilation. Following the blocked resource was more useful than staring at wait_type.

There was no classic long-running transaction

Compile-lock contention can produce a moving blocking chain rather than one session holding the same database lock throughout the incident.³

The SQL was valid

Nothing crashed when the procedure encountered:

1

SET ANSI_WARNINGS OFF;

The procedure worked during normal operation.

The failure only became visible when its execution behavior met enough concurrency.

That combination is exactly what makes intermittent database incidents expensive to diagnose from application symptoms alone.

A practical diagnostic sequence for THREADPOOL

When SQL Server periodically stops responding and THREADPOOL appears, we use it as a starting point rather than a diagnosis.

A useful investigation sequence is:

1. Capture the server state during the incident.
2. Check which requests are active and which are waiting.
3. Build the blocking chain.
4. Inspect blocking_session_id and the actual blocked resource.
5. Check what is consuming or retaining workers.
6. Do not discard a head blocker simply because its wait_type is empty.
7. If the blocked resource maps to a stored procedure, check for compile contention.
8. Capture recompilation events with Extended Events.
9. Inspect recompile_cause.
10. Correlate that cause with the procedure code and session settings.
11. Remove the source of contention before changing SQL Server capacity limits.

This sequence also avoids a common troubleshooting trap: treating a resource that happens to be exhausted as the component that needs to be enlarged.

Resource exhaustion is often several steps away from the defect

The same pattern appears in other database incidents.

A connection pool may fill because requests have stopped completing.

CPU may stay saturated because execution plans are unstable or statements are recompiling.

Storage latency may spike because a previously selective query has turned into a large scan.

Replication lag may be the visible problem while the actual change happened in write volume or transaction shape.

In this case, the visible metric was worker exhaustion.

The useful question was what kept the workers occupied.

Database troubleshooting should preserve the failure state

Intermittent production incidents are much easier to solve when the database is instrumented to retain evidence from the failure window.

For SQL Server, that usually means combining runtime session/request data with blocking information and targeted Extended Events rather than relying on a performance snapshot taken after recovery.

The investigation can then proceed from evidence:

1
2
3
4
5
6
7
8
9

What stopped progressing?
        ↓
What was it waiting for?
        ↓
Which resource was involved?
        ↓
Why was that resource contended?
        ↓
What is the smallest change that removes the cause?

That is also how we approach bounded database performance investigations at Techpipe: capture the failure first, reconstruct the dependency chain, and change the layer where the problem actually starts.

References