Microsoft SQL Server monitoring maturity model: from survival to expert

Most SQL Server outages are not exotic. The transaction log fills because nobody noticed log backups stopped. A head blocker sits idle with an open transaction while the worker thread pool drains. Error 825 appears in the error log for weeks before the disk actually fails. In each case, the signal was available; the monitoring just was not looking at it.

The difference between teams that get paged by users and teams that get paged by their own alerts is coverage depth, not tooling budget. This article lays out a four-level maturity model for SQL Server monitoring: Survival, Operational, Mature, and Expert. Each level adds a specific set of signals, and each exists because of failure modes the previous level cannot see.

Use this as a self-assessment. Find the level you are actually at, not the level your monitoring vendor claims you are at, and work out what it takes to move up one level. Moving from Survival to Operational is a weekend project. Moving from Mature to Expert is a mindset change.

flowchart TD
  L1[Level 1 - Survival
Is it up? Is it on fire?]
  L2[Level 2 - Operational
Is it healthy under load?]
  L3[Level 3 - Mature
Why is it degrading?]
  L4[Level 4 - Expert
What breaks next week?]
  L1 --> L2 --> L3 --> L4

How to use this model

The levels are cumulative. Each assumes everything below it is in place, alerting reliably, and being acted on. A team that monitors spinlock contention but has no alert on transaction log usage is not Expert; it is Survival with a hobby.

Two rules before the details:

  • Every signal needs an owner and a response. A metric nobody pages on and nobody trends is decoration. When you add a signal, decide in advance what threshold is actionable and what the first response is.
  • Cumulative counters need delta sampling. Many signals below come from sys.dm_os_performance_counters and sys.dm_os_wait_stats, which accumulate since instance startup. Reading cntr_value directly gives you a meaningless large number. Rates require two samples and a subtraction. Many teams sit at Level 2 believing they are at Level 3 because they collect wait stats but never delta them.

Level 1: Survival

Survival monitoring answers one question: is the instance alive and recoverable? It catches hard outages and nothing else. If this is all you have, you will still have bad nights, but you will not have data-loss surprises.

SignalWhat it catchesFirst check
Service stateEngine down, failed restart after patchingsqlservr process up; on Linux systemctl status mssql-server
Connectivity probeNetwork break, listener failure, total saturationsqlcmd -S localhost -Q "SELECT 1" -l 10
Database online stateSUSPECT, RECOVERY_PENDING, stuck RESTORINGsys.databases where state_desc <> 'ONLINE'
Severity 19+ errors in error logResource failures, fatal errors, corruptionsp_readerrorlog filtered for specific error numbers
One successful backup in 24hBroken backup job, full backup targetmsdb.dbo.backupset for backup_finish_date
Disk free on data/log/TempDB volumesThe most preventable outage classsys.dm_os_volume_stats joined to sys.master_files

Close these gaps first: check msdb.dbo.backupset rather than backup job status, because jobs report success while writing to a bad target. Watch for error 825 (read retry succeeded) in the error log, not only 823 and 824. Error 825 is the disk failing slowly, and it is often the only warning you get.

If you are here: you find out about outages from users, and post-mortems end with “we should have seen that earlier.” You are right, and Level 2 is how.

Level 2: Operational

Operational monitoring answers: is the instance healthy under its actual workload? This is the baseline any team running production SQL Server should hold. Missing these signals means you cannot tell a slow Tuesday from an incident until someone calls.

SignalWhat it catchesWarning sign
Batch requests/sec (delta)Throughput drop (upstream dead or engine unresponsive) or spike (retry storm)Sustained deviation beyond 2x or below 0.5x of baseline
Page Life ExpectancyBuffer pool memory pressure buildingDrop of 50%+ from baseline
Buffer cache hit ratioWorking set no longer fits in memoryBelow 95% on OLTP
Lock waits and deadlock countBlocking building, lock ordering problemsRising LCK_M_* waits; any sustained deadlock increase
Log percent used per databaseImminent log full, with log_reuse_wait_desc telling you whyAbove 70% with a reason other than NOTHING
CPU utilization (SQL vs other)CPU-bound workload vs external competitionSQL CPU sustained above 70%
AG send/redo queueReplication lag, failover RTO riskSustained growth on either queue
Top 5 wait categoriesWhere the engine is actually spending timeAny single wait type above 30-40% of total with latency impact

Two things make or break this level. First, baselines. Batch requests/sec and CPU mean nothing without a time-of-day and day-of-week baseline; a drop to near-zero during business hours is an outage, and the same reading at 3 a.m. Sunday is normal. Second, the log_reuse_wait_desc column. When log usage climbs, that column tells you whether the cause is missing log backups, an active transaction, replication lag, or an AG secondary falling behind. Adding disk space without reading it treats the symptom and leaves the cause running.

If you are here: you detect most incidents before users do, but diagnosis still takes hours because you can see that the engine is waiting, not precisely where and why.

Level 3: Mature

Mature monitoring answers: why is performance degrading, and for whom? The shift from Level 2 is granularity and attribution. Instead of top-5 waits sampled occasionally, you get the full wait breakdown delta-sampled at short intervals, plus the signals that attribute load to specific queries, files, and sessions.

  • Delta-sampled wait breakdown. Snapshot sys.dm_os_wait_stats every 30 seconds and compute deltas, excluding the benign idle waits. A cumulative read mixes last week’s backup window into today’s incident.
  • Memory grant waits. Memory Grants Pending and sys.dm_exec_query_memory_grants. Any sustained nonzero value means queries are parsed, optimized, and queued doing nothing while the application sees a hang. This is one of the most commonly missed signals in SQL Server.
  • TempDB by category. User objects, internal objects (spills), and version store separately, from tempdb.sys.dm_db_file_space_usage. Each category points at a different cause: application temp table use, bad memory grants spilling, or long-running transactions under RCSI.
  • Per-file I/O latency. sys.dm_io_virtual_file_stats delta-sampled, per database file, reads and writes split. Data file reads above 20ms or log writes above 5ms sustained is degraded; log writes above 20ms directly tax every commit.
  • Blocking and head-blocker detection. Not just “blocking exists” but chain depth, duration, and whether the head blocker is sleeping with an open transaction. A sleeping head blocker does not self-resolve and is the usual start of the worker-exhaustion cascade.
  • Worker and runnable backlog. Active workers versus max_workers_count, plus runnable_tasks_count per scheduler from sys.dm_os_schedulers. Runnable backlog is the in-engine CPU queue that OS CPU metrics miss entirely; you can have low OS CPU with every scheduler queuing.
  • Query Store plan regression detection. SQL 2016 and later. Query Store persists plan history across restarts, which DMVs do not, and lets you detect and force regressing plans instead of rediscovering them every incident.
  • suspect_pages. msdb.dbo.suspect_pages is a durable record of 823 and 824 failures (bad checksum, torn page) that survives restarts and log recycling. Any new row is pageable.
  • Certificate expiry. sys.certificates with expiry_date. Expired certificates break AG endpoint authentication and backup encryption, and they tend to expire during an incident rather than during business hours.

If you are here: your incident reviews name the query, the file, or the session that caused the problem, and mean time to diagnose drops from hours to minutes. What remains is predicting problems before they produce symptoms.

Level 4: Expert

Expert monitoring answers: what breaks next week? These signals are about distribution, drift, and early warning. They come from having been burned by failure modes that look fine at every aggregate level.

  • Per-NUMA PLE. The instance-wide Buffer Manager PLE is an average across NUMA nodes. One node can be thrashing at a PLE of 200 while the aggregate reads 695 and looks healthy. On multi-socket hosts, monitor Buffer Node PLE per node.
  • Signal-wait trending. signal_wait_time_ms as a percentage of total wait time, trended over weeks. This is time spent on the runnable queue after the resource was available: pure CPU scheduler pressure. A slow rise from 5% to 15% means you are losing CPU headroom invisibly, especially on VMs where steal time hides it. Above 20% indicates active CPU pressure.
  • VLF count per database. From sys.dm_db_log_info (SQL 2016 SP2+). Thousands of small auto-growths produce thousands of virtual log files, which slow crash recovery, backup, and restore. This does nothing for months, then turns a 2-minute restart into a 2-hour one at the worst moment. Above 1000, plan to consolidate; above 10,000, expect recovery to be significantly slow.
  • Spinlock contention. sys.dm_os_spinlock_stats. Spinlock contention shows up as high CPU with no matching workload increase and does not appear in wait statistics at all. On high-throughput systems it is the explanation for “CPU is at 100% but throughput is flat.”
  • Version store growth rate. Not just current size but rate, per database, from sys.dm_tran_version_store_space_usage. A linear climb means a long-running transaction under RCSI or snapshot isolation is accumulating versions, and you can compute exactly when TempDB fills instead of being surprised by it.
  • Predictive PLE. Trend PLE over days and extrapolate to your threshold, so memory pressure becomes a planned capacity conversation instead of a spiral at peak load. This is a trending practice on top of the PLE counter, not a built-in SQL Server feature.

If you are here: your pages are mostly leading indicators, and capacity problems arrive as tickets with runway estimates attached.

Common traps at every level

  • Reading cumulative counters as rates. Batch Requests/sec in the DMV is cumulative since startup. If your dashboard shows a number in the billions, you are not at the level you think.
  • Alerting on CPU alone. Low CPU with high latency is usually blocking or I/O starvation and is routinely missed. High CPU is often legitimate: backups, ETL, cold starts.
  • Monitoring without delta persistence. DMV data resets on restart. If your only copy of wait stats and I/O stats lives inside the instance, every restart destroys your forensic record.
  • Letting maintenance page you. CHECKDB, index rebuilds, and backups legitimately spike I/O, CPU, and lock waits. Suppress maintenance windows in alerting rather than disabling the signals.

How Netdata helps

  • Netdata samples the cumulative SQL Server counters (sys.dm_os_performance_counters, wait stats, per-file I/O stats) at short intervals and stores the computed rates externally, so a restart does not erase your forensic history and you never read a raw cumulative value by mistake.
  • Wait statistics are delta-sampled and broken down by wait type, which is the Level 3 discipline most teams implement by hand, including the signal_wait_time_ms split that exposes CPU scheduler pressure at Level 4.
  • Per-database log usage, TempDB space by category, memory grants pending, and worker thread utilization are charted together, so the blocking-to-thread-exhaustion cascade and the memory-pressure spiral show up as correlated shapes instead of unrelated graphs.
  • External persistence of per-file I/O latency and PLE trends supports the Level 4 practice of extrapolating runway before thresholds are crossed.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.