How Microsoft SQL Server actually works in production: a mental model for operators

Most SQL Server incidents look confusing because operators bring a Linux-process mental model to a system that is not one. sqlservr (sqlservr.exe on Windows) is a single multi-threaded process, but inside it runs SQLOS, a user-mode operating system with its own scheduler, memory manager, and I/O completion handling. The host OS does not schedule your queries, does not cache your data pages, and does not decide which transaction gets a lock. SQL Server does all of that itself.

The failure modes you are paged for almost never live inside one subsystem. A log-full outage is a backup-chain problem that becomes a write outage. A blocking cascade is a lock-manager problem that becomes a worker-thread problem that becomes a “database is down” problem. If you only watch OS-level metrics (CPU%, memory%, disk queue), you will see the smoke but not the fire.

This article builds the operator’s mental model: the subsystems, what they compete for, and why “SQL CPU pressure differs from OS CPU” is the root cause of most misdiagnosis. The runbook articles linked at the end assume this model.

One process, its own operating system

SQLOS sits between the SQL engine and the host OS. Everything operationally interesting happens at the SQLOS layer; the host OS sees an opaque process consuming CPU and RAM.

The subsystems you need in your head:

1. Cooperative schedulers: one per logical CPU, non-preemptive.
2. Worker threads: a bounded pool that executes all requests.
3. Buffer pool: the 8KB page cache, by far the largest memory consumer.
4. Transaction log: write-ahead log divided into virtual log files (VLFs).
5. TempDB: shared scratch database for spills, temp tables, and row versioning.
6. Memory grant pool: workspace memory for sorts, hashes, and certain joins.
7. Lock manager: hierarchical locking from row to database.
8. AlwaysOn AG machinery (if deployed): log transport and redo on secondaries.

flowchart TD
  Client[Client connections] --> Sched[SQLOS schedulers - one per logical CPU]
  Sched --> Workers[Worker thread pool - bounded]
  Workers --> BP[Buffer pool - 8KB page cache]
  Workers --> LG[Lock manager - row to page to table]
  Workers --> Grant[Memory grant pool]
  Grant -. spill on underestimation .-> TDB[TempDB - shared scratch]
  Workers --> TLog[Transaction log - WAL, VLFs]
  TDB --> Disk[(Data and log volumes)]
  TLog --> Disk
  BP --> Disk
  TLog -. log stream .-> AG[AlwaysOn secondary replicas]

SQLOS schedulers and worker threads

SQL Server does not use OS thread scheduling for query execution. It runs one cooperative scheduler per logical CPU. Each scheduler has a run queue, a waiter list, and an I/O completion list. Worker threads yield voluntarily at known yield points. When a worker must call external code (CLR, extended stored procedures, linked servers), it transitions to preemptive mode, which is expensive and tracked separately.

Every active request needs a worker thread, and the pool is bounded. The default maximum on 64-bit systems for 4-64 logical CPUs is 512 + ((logical_CPUs - 4) * 16), which lands most systems in the 512-2048 range. Parallel queries multiply consumption: a MAXDOP 8 query can hold up to 8 workers at once.

Two consequences:

• Worker exhaustion is a cliff, not a slope. Performance is normal until the pool is nearly exhausted, then new requests queue on the THREADPOOL wait type, which is operationally equivalent to connection refusal. CPU and I/O can both look idle while the instance refuses work, because the workers are suspended on locks, not running. Worse, THREADPOOL waits may not be recorded in sys.dm_os_wait_stats if no thread is free to record them. Watch active worker count directly.
• CPU pressure inside SQL Server is invisible to OS CPU%. You can have low OS CPU with every scheduler saturated because all workers are waiting on I/O or locks. The cleanest in-engine CPU queue signal is runnable_tasks_count in sys.dm_os_schedulers: tasks that have their resource and are waiting only for CPU time. Sustained values above 1 per scheduler mean real CPU queuing, and on VMs this can be elevated even when the host reports headroom (steal and co-tenant contention are invisible to the guest). The detailed diagnosis is in SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss.

When workers do run out, the Dedicated Admin Connection (DAC) bypasses normal connection limits and is the only reliable way in. Know it before you need it.

The buffer pool and the memory pressure spiral

The buffer pool is SQL Server’s cache of 8KB data pages. It is the single largest memory consumer and the primary determinant of I/O behavior: a page request that misses the cache becomes a physical read.

Two metrics describe its health. Page Life Expectancy (PLE) is how many seconds a page survives before eviction. Buffer cache hit ratio is the fraction of page requests served from memory. The lazy writer is the background process that evicts pages under memory pressure; when it is active outside checkpoint windows, the pool is under stress.

The failure mode to internalize is the memory pressure spiral, because it is self-reinforcing:

1. Something consumes buffer pool memory: a surge of large memory grants, external OS pressure, or a misconfigured max server memory.
2. The pool shrinks, PLE drops, and pages are evicted faster.
3. Queries that previously hit cache now trigger physical reads; PAGEIOLATCH_* waits climb.
4. The I/O subsystem, sized for the normal read rate, saturates. Latency rises.
5. More concurrent sessions pile up, each demanding pages, further pressuring the pool.

The distinguishing test: if PLE is stable but I/O stalls are high, storage is the problem. If PLE is dropping and I/O stalls are rising, it is the spiral. One gotcha on multi-socket hardware: on NUMA systems, the instance-wide Buffer Manager PLE is an average across nodes. One node can be thrashing while the aggregate looks fine. Check per-node PLE via Buffer Node.

The plan cache lives in the same memory budget. A flood of single-use ad-hoc plans pollutes it, drives compilations, and can help trigger the spiral. See SQL Server high compilations per second: plan cache pollution and CPU burn.

The transaction log: write-ahead, VLFs, and the cliff

Every modification is written to the transaction log before it reaches data files (write-ahead logging). The log is divided into Virtual Log Files (VLFs). Space is reused after a log backup (full/bulk-logged recovery) or after a checkpoint (simple recovery).

If the log cannot be truncated, it grows until it fills its volume, and then every write to that database halts with error 9002. This is the single most common cause of “unexpected” SQL Server outages, and it is entirely preventable. The cause is never mysterious: sys.databases.log_reuse_wait_desc tells you exactly what is blocking truncation. The usual suspects are LOG_BACKUP (no log backup taken), ACTIVE_TRANSACTION (a long-running transaction), and AVAILABILITY_REPLICA or REPLICATION (a downstream consumer has not read the log yet).

Two operational details:

• Auto-growth is expensive and does not save you. When a file grows, all I/O to it pauses while the new space is initialized, and Instant File Initialization does not apply to log files, so the new extent is zero-initialized. Frequent small growths also fragment the log into thousands of VLFs; a VLF count above roughly 1000 measurably slows recovery, backup, and restore. Pre-size logs and use large fixed growth increments.
• The log has a cliff-edge degradation curve. It works normally until 100%, then writes stop instantly. The leading indicators are log-used percent trending up, auto-growth events, and a non-NOTHING log_reuse_wait_desc.

TempDB: the shared scratch database

TempDB is one shared system database used by every database on the instance for temp tables, table variables, sort/hash spills, internal worktables, and row versioning (snapshot isolation, RCSI, readable AG secondaries). It is recreated on every restart.

It fails in two distinct ways:

• Contention. Latch waits on allocation pages (PFS, GAM, SGAM) show up as PAGELATCH_UP/PAGELATCH_EX waits on pages in database ID 2. This is logical serialization, not I/O or CPU saturation, and it is a classic scalability bottleneck on high-concurrency OLTP. Microsoft’s starting recommendation is one data file per logical CPU up to 8, then add in groups of 4 if contention persists.
• Exhaustion. When TempDB runs out of space, queries across all databases halt. Space breaks down into user objects, internal objects (spills), and the version store, queryable via sys.dm_db_file_space_usage in the tempdb context. Version store growth usually means a long-running transaction under snapshot isolation; internal-object growth means queries are spilling, which traces back to memory grants.

The full triage is in SQL Server TempDB full: the shared scratch database that halts every query.

Memory grants: the invisible queue

Before executing, queries involving sorts, hashes, or certain joins request a memory grant from a pool separate from the buffer pool. If the pool is exhausted, the query waits on RESOURCE_SEMAPHORE before it can even begin. If the grant is underestimated because of cardinality estimation errors, the operation spills to TempDB, trading a memory wait for I/O latency.

This is one of the most commonly missed signals because Memory Grants Pending is zero almost all the time. When it goes nonzero, queries are silently queued: parsed, optimized, and stuck. Users report “the database is slow” while CPU and I/O look unremarkable. A single bad plan with a massive overestimated grant can hoard the pool and starve everyone else.

The lock manager and the blocking cascade

Locking is hierarchical: row, page, table, database. Lock escalation fires when a single transaction holds roughly 5000 row/page locks on a table and converts them to a table lock. The deadlock monitor runs every 5 seconds (down to 100ms under heavy contention) and picks a victim by estimated transaction cost, not at random.

The failure archetype that matters most is the blocking cascade into worker thread exhaustion, and it is the purest example of “incidents live between layers”:

1. A session holds a lock. The dangerous case is a sleeping head blocker: an idle connection with an uncommitted transaction, almost always an application bug or an abandoned SSMS session.
2. Other sessions requesting conflicting locks go to suspended on LCK_M_* waits. Each holds a worker thread.
3. The worker pool drains. Batch requests keep arriving but transactions/sec drops because nothing completes. CPU falls, because suspended workers do not run.
4. The pool exhausts. New requests hit THREADPOOL waits. The instance looks dead with low CPU and low I/O.

That last symptom, “low CPU, low I/O, nothing processing”, is the signature. An operator watching only OS metrics will see a healthy box. Killing the head blocker resolves it, but be warned: rollback can take as long as the original transaction, and the sessions stay blocked until it finishes. The worker-exhaustion endgame is covered in SQL Server THREADPOOL waits: worker thread exhaustion and refused connections and SQL Server worker thread exhaustion: when the instance stops accepting work.

AlwaysOn: the log stream with two queues

With Availability Groups, the primary accepts writes and streams log records to secondaries, which redo them. Two queues define the health of the system:

• Send queue: log generated on the primary but not yet sent. Growth means the network or the secondary’s hardening cannot keep up.
• Redo queue: log received but not yet applied on the secondary. Growth means the secondary is CPU- or I/O-bound, and on failover the new primary must apply the entire queue before becoming available. That is your RTO, regardless of what the “healthy” label says.

In synchronous commit mode, the primary waits for the secondary to harden the log before acknowledging the commit. Secondary performance is therefore on the primary’s write-latency critical path, surfaced as HADR_SYNC_COMMIT waits. A slow secondary makes a fast primary slow.

Why SQL CPU pressure is not OS CPU pressure

This is the single highest-leverage idea in the model:

• OS CPU% measures thread time on cores. It tells you nothing about queue depth inside SQL Server.
• runnable_tasks_count per scheduler measures how many tasks have their resource and are waiting only for a core. That is real CPU pressure.
• signal_wait_time_ms in wait stats is the same idea aggregated: time on the runnable queue after the resource became available. Signal waits above about 20% of total wait time indicate CPU pressure.
• Low OS CPU plus high latency usually means the workers are waiting on locks, I/O, or memory grants, not cores. That is a bigger and more commonly missed problem than a hot CPU.
• On VMs, steal time hides pressure from the guest entirely; you will see SOS_SCHEDULER_YIELD waits and runnable backlog while reported CPU looks modest.

For the query-load side of CPU, see SQL Server CPU utilization high: telling query load apart from a bad plan, and for the parallelism side see SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong.

What competes for what

Every resource in the instance has multiple legitimate consumers. Incidents start when one consumer crowds out the rest:

A note on reading the counters behind all of this: most rate counters in sys.dm_os_performance_counters (Batch Requests/sec, Transactions/sec, Compilations/sec) are cumulative since startup, not rates. Reading cntr_value directly gives a meaningless large number; you must snapshot twice and compute the delta. The same applies to wait stats and I/O stall counters.

Deployment variants that change the picture

• Standard vs Enterprise: Resource Governor, partitioning, and buffer pool extension are Enterprise features (Resource Governor reached Standard in SQL Server 2025). Edition caps on memory and cores directly bound the buffer pool.
• On Linux: same engine, different OS signals (/proc instead of perf counters, systemctl status mssql-server, Pacemaker instead of WSFC for AG). In containers, be aware that older builds did not respect cgroup v2 memory limits; SQL Server saw host RAM and got OOM-killed.
• Named instances: separate ports, error logs, and resource pools; performance counter object names change to MSSQL$INSTANCENAME:.
• OLTP vs data warehouse: radically different healthy profiles. A warehouse legitimately has a lower buffer cache hit ratio, high read I/O, large memory grants, and long-running queries. Thresholds must follow workload type.
• Azure SQL Database / Managed Instance: same engine lineage, but storage is abstracted and the DMV surface differs. This model targets the self-managed engine.

Signals to watch in production

These signals map directly to the subsystems above, and together they cover the model:

How Netdata helps

The mental model above is only useful if your monitoring reflects the same layers. Netdata collects SQL Server signals per subsystem so you can correlate across them during an incident instead of querying DMVs one at a time:

• Wait statistics and wait-type breakdowns, so you can see RESOURCE_SEMAPHORE, LCK_M_*, PAGEIOLATCH_*, or THREADPOOL become dominant in the same window as user-visible symptoms.
• Buffer pool health (PLE, cache hit ratio) alongside per-file I/O stall, so the memory pressure spiral shows up as two correlated curves rather than a mystery latency rise.
• Worker and connection metrics, so a blocking cascade reads as “connections stable, workers climbing, CPU falling” in one view.
• Transaction log usage per database, giving you the cliff-edge warning before error 9002.
• Blocking and lock-wait visibility, so head blockers are caught before the worker pool drains.
• Per-second granularity and ML anomaly detection on these metrics, which matters because the failure archetypes in this article progress in minutes.

Microsoft SQL Server monitoring with Netdata brings these signals together.

Related guides

• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server high compilations per second: plan cache pollution and CPU burn
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss
• SQL Server TempDB full: the shared scratch database that halts every query
• SQL Server THREADPOOL waits: worker thread exhaustion and refused connections
• SQL Server worker thread exhaustion: when the instance stops accepting work