$ guides / microsoft-sql-server ▌

MICROSOFT SQL SERVER · OPERATIONS PLAYBOOK

Keeping SQL Server up: the log that fills, the memory that spirals, and the workers that run out

A single engine running its own user-mode OS — cooperative schedulers, a buffer pool, a write-ahead log, a shared TempDB, and a lock manager — where the most common outage is a transaction log nobody backed up, and the scariest one is an instance that stops responding while CPU sits idle. We trace how the engine really behaves under load, where it turns from slow into stopped, and what to do when it does.

> Start with the monitoring checklist → # Jump to the full guide list

SQL Server's defaults get you to production quickly, then hand you a set of cliff-edges that most teams only discover during an incident.

The defaults work. Until a database in full recovery model runs for a week with no log backup, the transaction log fills the volume, and every write fails with Error 9002. Until a bad plan's memory grant evicts the buffer pool, Page Life Expectancy collapses, physical reads saturate the disk, and the whole instance spirals. Until one uncommitted transaction from a connection returned to the pool blocks dozens of sessions, drains the worker pool, and the engine stops accepting connections with THREADPOOL waits while CPU and I/O sit idle. Until a single TempDB data file turns allocation-page latching into a cluster-wide bottleneck. Until Error 825 quietly reports that a read succeeded only after retrying — the disk is failing, and nobody is watching for it.

These guides are written for engineers who already run SQL Server, not for people learning what an index is. The goal is the mental model of how the engine actually behaves under load, the failure patterns that keep recurring, the monitoring story that catches them before they page anyone, and the runbooks you wish someone had handed you before your last incident.

How SQL Server actually runs in production

SQL Server is one process running its own user-mode operating system, SQLOS. Client requests are scheduled cooperatively onto workers, compiled into plans that request memory, take locks, read and write 8KB pages through the buffer pool, and are made durable by a write-ahead log — with TempDB as shared scratch underneath all of it. Most production failures live between these layers, not inside any one of them.

clients / connections

Applications, pools, and jobs connect over TDS (default port 1433; named instances use dynamic ports and the SQL Server Browser on UDP 1434). Pooling means the engine sees pool-size x app-instances, not end users. Each active request needs a worker before it can do anything.

CLIENT

SQLOS schedulers + workers

One cooperative scheduler per logical CPU. Workers yield at known points; when all workers are busy, new requests queue on <code>THREADPOOL</code> — connection refusal. <code>SOS_SCHEDULER_YIELD</code> and <code>runnable_tasks_count</code> are the in-engine CPU-pressure signals, which differ from OS CPU%.

SCHEDULER

optimizer + plan cache

Queries are compiled into plans and cached. Non-parameterized SQL pollutes the cache; a plan compiled for one parameter value can be catastrophic for the next (parameter sniffing). Sorts, hashes and joins request a memory grant here before they can run.

OPTIMIZER

lock manager

Hierarchical locks (row → page → table). Too many locks on one table escalate to a table lock; conflicting requests wait as <code>LCK_M_*</code>. A blocking chain behind one head blocker can cascade to the whole workload; a background monitor breaks true cycles as deadlocks (Error 1205).

LOCK

buffer pool + memory

The 8KB-page cache and the single largest memory consumer. Page Life Expectancy measures how long pages survive; a miss is a physical read (<code>PAGEIOLATCH</code>). The query-memory grant pool is separate — exhaust it and queries queue on <code>RESOURCE_SEMAPHORE</code>.

MEMORY

transaction log (WAL)

Every change is written to the log before the data files. Space is reused only after a log backup (full recovery) or checkpoint (simple); <code>log_reuse_wait_desc</code> says why it can't. If it can't truncate, it grows until the disk is full and writes stop. <code>WRITELOG</code> waits bound commit throughput.

LOG

TempDB

One shared scratch database for sort/hash spills, temp objects, and row versioning (RCSI, snapshot, readable secondaries). Latch contention on its allocation pages (PFS/GAM/SGAM) is a classic bottleneck; running it out of space halts queries across every database.

TEMPDB

storage + database files

Data and log files on disk. <code>dm_io_virtual_file_stats</code> measures per-file latency; log-write latency directly limits commits. Errors 823/824 are hard I/O or consistency corruption; 825 is the soft read-retry warning that usually comes first. AlwaysOn ships this log to secondaries.

STORAGE

Why this matters: 'SQL Server is slow' can come from CPU scheduler pressure, a memory-starved buffer pool, a log that can't truncate, TempDB allocation-latch contention, a blocking chain behind one idle session, genuinely slow storage, or a synchronous AlwaysOn secondary throttling every commit. The symptom rhymes but each layer has a different signal — and a different fix.

The failures you'll actually see

Most SQL Server incidents fall into a small set of recurring patterns. Recognise the shape, and triage gets dramatically faster.

CRITICAL

The transaction log full outage

A database in full recovery model can't truncate its log — a missing log backup, a long-running transaction, or an AG secondary that hasn't read it — so the log grows until it fills the volume and every write fails with Error 9002. Reads may still work, which masks it. The single most common unexpected SQL Server outage, and it is entirely preventable. If TempDB's log fills, every database is affected at once.

Error 9002 'transaction log is full' in the error log and to clients
log_reuse_wait_desc = LOG_BACKUP / ACTIVE_TRANSACTION / AVAILABILITY_REPLICA
Percent Log Used at or near 100% with a rising trend
All writes on the database failing while reads still succeed

Investigate →

ACTIVE

The memory pressure spiral

Something consumes the buffer pool — a bad plan's oversized memory grant, external OS pressure, a VM balloon driver — so pages evict, cache hits turn into physical reads, the I/O subsystem saturates, latency climbs, more sessions pile on, and the pressure compounds. CPU often looks low because this is I/O-bound. Queries waiting for a memory grant queue on RESOURCE_SEMAPHORE and appear hung to the application.

Page Life Expectancy dropping sharply from baseline
PAGEIOLATCH_SH / PAGEIOLATCH_EX becoming dominant waits with rising I/O stalls
RESOURCE_SEMAPHORE waits and Memory Grants Pending above zero
Low-to-moderate CPU while query latency climbs

Investigate →

CRITICAL

Worker thread exhaustion

Every request needs a worker. A blocking chain or a flood of external waits pins workers until the pool drains; new requests then queue on THREADPOOL and the instance stops accepting connections — it looks completely down while CPU and I/O are idle. The Dedicated Admin Connection (port 1434) is your way in. Raising the worker limit is not the fix; finding what consumes them is.

THREADPOOL waits and work_queue_count > 0 on schedulers
New connections timing out or refused; the instance appears hung
Batch requests arriving but transactions/sec falling to near zero
CPU and I/O low while the system is unresponsive

Investigate →

CRITICAL

TempDB contention and exhaustion

TempDB is shared by every database. With a single data file on a multi-core box, allocation-page latching (PAGELATCH_UP/EX on database ID 2) serializes throughput and looks like CPU or I/O saturation but is neither. Run TempDB out of space — sort/hash spills, temp-table abuse, or a long transaction bloating the version store — and queries fail across the entire instance.

PAGELATCH_UP / PAGELATCH_EX waits on pages in database_id 2
TempDB free space falling; Error 1105 / 3958 on queries
Version store growing (long-running snapshot/RCSI transactions)
Internal-object space high (aggressive sort/hash spills)

Investigate →

CRITICAL

Silent storage corruption

The storage medium is deteriorating. Error 825 reports that a read succeeded only after retrying — the canary most teams don't monitor — and it usually precedes a hard Error 823 (I/O error) or 824 (logical consistency / bad checksum). By the time the hard error fires, data may already be lost. Every event is recorded durably in msdb.dbo.suspect_pages, which survives the restart that wipes the error log.

Error 825 'read succeeded after failing N time(s)' in the error log
Error 823 / 824 on data or log files
New rows in msdb.dbo.suspect_pages
Rising I/O stalls or a database dropping to SUSPECT

Investigate →

ACTIVE

The blocking and deadlock cascade

One session holds a lock — often a sleeping head blocker with an uncommitted transaction — and conflicting sessions queue behind it as LCK_M_* waits, each pinning a worker. Left alone the chain deepens toward worker exhaustion; when two sessions each hold what the other needs, the monitor aborts one with Error 1205 (deadlock victim). CPU and I/O look idle while nothing completes.

Error 1205 'chosen as the deadlock victim' returned to clients
LCK_M_* waits rising with a growing blocking-chain depth
A sleeping head blocker holding locks under an open transaction
Transactions/sec falling while batch requests keep arriving

Investigate →

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free

SQL Server monitoring maturity levels

SQL Server observability works in four practical levels. Each is a complete operation, not a stepping stone. Pick the level that matches how much your instance matters. Most production instances should land at the second level.

Level 1: Survival

Know that something is wrong

Survival monitoring is the floor. With these signals you can answer one question: is the instance up, are its databases online, and can it be recovered? You will not learn what broke, but you will learn that something broke before users do. Survival is enough for dev instances and non-critical databases.

Service / instance responsiveness Does a TCP connect + SELECT 1 on port 1433 succeed?
Database online state Any production DB in SUSPECT / RECOVERY_PENDING / OFFLINE?
Error log severity 19+ / 823-825 Fatal resource errors and storage corruption signatures.
Backup in the last 24 hours Is there any restore point at all right now?
Disk free on data / log / TempDB The volumes whose exhaustion halts writes.

Level 2: Operational

Diagnose most incidents on your own

Operational monitoring is what most production instances should target. Survival tells you something is wrong; operational tells you what. With this coverage your team can usually diagnose an incident on its own: log pressure, memory pressure, blocking, deadlocks, and CPU.

Transaction log percent used + reuse wait Log used per DB with log_reuse_wait_desc — the 9002 precursor.
Top wait statistics (delta-sampled) The Rosetta Stone; exclude benign waits, compute deltas.
Page Life Expectancy Buffer-pool memory pressure; watch the trend, per NUMA node.
Buffer cache hit ratio Working set fit — read with workload context.
Lock wait time + deadlocks/sec Blocking measured from the wait side; 1205 events.
Batch requests/sec + transactions/sec Throughput baseline; divergence means work isn't completing.
CPU (SQL vs other) utilization Is SQL Server the bottleneck, or a competing process?
AG synchronization health If AlwaysOn is configured: replica state and lag.

Level 3: Mature

Catch problems before they become incidents

Mature monitoring catches problems before they wake anyone up. Memory grants queuing, TempDB drifting, a head blocker forming, per-file I/O creeping, a plan quietly regressing. None of these page you on day one. They become page-out incidents on day thirty.

Wait breakdown at 30-second granularity Delta sampling turns cumulative waits into a live signal.
Memory grants pending + RESOURCE_SEMAPHORE Queries queued for workspace memory before they run.
TempDB space by category User vs internal objects vs version store.
Per-file I/O latency dm_io_virtual_file_stats per file, not an aggregate.
Blocking chain + head-blocker detection Is the head blocker sleeping with an open transaction?
Worker + runnable scheduler backlog Approach to THREADPOOL; in-engine CPU queue depth.
Query Store plan regressions Parameter sniffing and plan changes over time (2016+).
Backup freshness, suspect_pages, cert expiry Recoverability and durable corruption / expiry records.

Level 4: Expert

Reactive instrumentation after real incidents

Expert signals enter your stack the day after a specific incident proved you needed them. Per-NUMA memory balance, version-store growth rate, VLF counts, spinlock contention, predictive PLE trending. Most teams never need every signal here. Add the ones your incident history says you do.

Per-NUMA-node PLE distribution The instance PLE is an average; one node can starve.
Signal wait percentage trend Signal-vs-resource wait split as a CPU-pressure lead.
Version store growth rate RCSI/snapshot workloads bloating TempDB over time.
VLF count per database Log fragmentation that slows recovery and failover.
Spinlock contention High CPU with low useful throughput at high concurrency.
Query Store forced-plan success Automatic plan correction actually holding regressions.
Forwarded records/sec Heap fragmentation multiplying I/O.
Predictive PLE / log runway trending Days-to-threshold extrapolation from the trend.

Operating mistakes worth avoiding

The traps SQL Server teams keep falling into. Each has a clear, well-known fix. Most teams only learn it after an incident.

Judging health by CPU % alone

SQL Server is designed to use available CPU, so 100% is not automatically a problem — and low CPU with high latency is usually the bigger one, because the engine is stalled on blocking, I/O, or memory grants. The pulse of the server is <code>sys.dm_os_wait_stats</code>: a team that watches CPU/RAM/disk counters but never trends wait statistics is flying blind about how those resources are actually being consumed.

Adding log space without reading log_reuse_wait_desc

When the log fills, teams add space and move on — but the root cause is almost always a missing log backup, a long-running transaction, or replication/AG lag. <code>sys.databases.log_reuse_wait_desc</code> names the exact reason (LOG_BACKUP, ACTIVE_TRANSACTION, AVAILABILITY_REPLICA). Adding space without reading it just delays the next <code>Error 9002</code>.

Reading Batch Requests/sec as a rate

The counter in <code>sys.dm_os_performance_counters</code> is cumulative since startup (cntr_type 272696576). Teams that read <code>cntr_value</code> directly get a meaningless ever-growing number and a flat dashboard. You must take two samples and divide by elapsed seconds — the same applies to transactions/sec, compilations/sec, and every I/O-stall counter.

Running a single TempDB data file on a multi-core box

One TempDB data file serializes allocation on the PFS/GAM/SGAM bitmap pages, producing <code>PAGELATCH_UP/EX</code> contention that masquerades as CPU or I/O saturation but is purely logical. Microsoft's guidance is one equally-sized data file per logical CPU up to 8, added in groups of 4 if contention persists — one of the highest-leverage, lowest-risk fixes there is.

Watching only the average PLE on a NUMA box

On a multi-NUMA server the instance-wide <code>Buffer Manager</code> Page Life Expectancy is an average across nodes, not a minimum. One <code>Buffer Node</code> can be under severe memory pressure while the aggregate looks perfectly healthy. Without per-node PLE you are blind to the starved node driving your latency.

Ignoring Error 825

<code>Error 825</code> means a read succeeded only after one or more retries — SQL Server's canary that the disk is failing. Most teams alert on the hard errors 823/824 but never on 825, so they get the warning as an outage instead of a heads-up. By the time 823/824 fire, data may already be lost. Alert on any 825, and check <code>msdb.dbo.suspect_pages</code>.

Treating auto-growth as normal

Alerting on low disk space is too late; the alert should be on the auto-grow event itself, because SQL Server pauses all I/O to a file while the new space is initialized — and Instant File Initialization does NOT apply to log files, so the whole new log extent is zeroed. Pre-size data and log files and set sane fixed growth increments instead of leaving percentage defaults.

Assuming AlwaysOn means automatic

Teams stand up an Availability Group and assume failover is guaranteed, but never validate WSFC (or Pacemaker) quorum, the health-check timeout and failover-condition level, or endpoint-certificate expiry. A broken cluster or misconfigured quorum means no automatic failover — the protection they think they have doesn't exist until the incident proves it.

SQL Server runbooks in this section

Each guide is a focused runbook for one symptom or topic. Pick one when you have an incident, or use the categories to learn the area.

▸

Start here

▸

Transaction log and disk space

▸

Memory and the buffer pool

▸

CPU, schedulers, and compilation

▸

Worker threads and connections

▸

TempDB

▸

Locking, blocking, and deadlocks

▸

Storage I/O and corruption

▸

Query plans and the optimizer

▸

Availability, backup, and recovery

▸

AlwaysOn Availability Groups

▸

Authentication, privilege, and encryption

WHERE TO GO NEXT

Setting up SQL Server monitoring, or putting out a fire?

If you're starting from scratch, the monitoring checklist is the path of least regret. If you're mid-incident, jump straight to the symptom that matches what you're seeing.

> Start with the checklist > Back to Operations Guides