Performance Counters, OS Metrics, Storage Latency, Memory Pressure, and CPU Scheduling

Learning outcomes

A ServiceHub report says “SQL Server is using too much CPU.” The host chart shows 85% CPU, one database file has high cumulative I/O stall, and Page Life Expectancy is lower than yesterday. None of those facts independently identifies a bottleneck. SQL Server runs inside an operating system, VM or container with its own CPU scheduling, memory pressure and storage path. Observability becomes useful when engine counters and host metrics describe the same interval and you understand whether each counter is instantaneous, cumulative, sampled or derived.

1. SQL performance counters need their counter type

sys.dm_os_performance_counters exposes the raw values SQL Server provides to the Windows Performance Counter engine. Some counters are instantaneous; others are cumulative bases from which a rate or ratio must be calculated. For a “per second” counter, one raw cntr_value is not the per-second rate. Sample twice and divide the delta by elapsed time. The cntr_type tells you how the value must be interpreted.

SELECT    object_name,    counter_name,    instance_name,    cntr_value,    cntr_typeFROM sys.dm_os_performance_countersWHERE counter_name IN(    N'Batch Requests/sec',    N'Page life expectancy',    N'Page reads/sec',    N'Page writes/sec',    N'User Connections',    N'Log Flushes/sec')ORDER BY object_name, counter_name, instance_name;

Do not turn Page Life Expectancy into a universal magic threshold. Memory size, workload, columnstore/In-Memory OLTP usage and engine version change its meaning. A downward shift that aligns with query regressions, physical reads and memory-pressure signals is more useful than one isolated number.

2. File I/O DMVs expose cumulative stall, not a storage latency percentile

sys.dm_io_virtual_file_stats tracks read/write counts and stall time per database file since engine start (or the documented reset condition). Dividing cumulative stall by cumulative I/O count yields a coarse average over that entire observation lifetime. It is useful for comparing files and spotting changes, but it is not a p95/p99 latency measurement and cannot distinguish every storage-layer queue.

SELECT    DB_NAME(vfs.database_id) AS database_name,    mf.name AS logical_file_name,    mf.type_desc,    vfs.num_of_reads,    CAST(vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads,0) AS decimal(18,2)) AS avg_read_ms,    vfs.num_of_writes,    CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes,0) AS decimal(18,2)) AS avg_write_ms,    vfs.num_of_bytes_read,    vfs.num_of_bytes_written,    vfs.sample_msFROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfsJOIN sys.master_files AS mf  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_idORDER BY avg_read_ms DESC;

Corroborate with wait types such as PAGEIOLATCH_..., query plans, physical read counts, storage metrics and workload changes. Slow average file I/O can be cause, consequence, or simply historical residue from a past event.

3. Memory pressure is a system state, not “SQL uses lots of RAM”

SQL Server deliberately caches data and plans, so a large working set is expected. sys.dm_os_process_memory reports process-level memory and explicit low-memory signals. sys.dm_os_sys_memory reports host-level physical/commit state. In a container or VM, also inspect the effective guest/cgroup limits because host physical memory may not equal what SQL Server can actually consume.

SELECT    physical_memory_in_use_kb,    memory_utilization_percentage,    available_commit_limit_kb,    process_physical_memory_low,    process_virtual_memory_lowFROM sys.dm_os_process_memory;SELECT    total_physical_memory_kb,    available_physical_memory_kb,    total_page_file_kb,    available_page_file_kb,    system_low_memory_signal_state,    system_memory_state_descFROM sys.dm_os_sys_memory;

If low-memory signals are not set, a full buffer pool is not evidence of memory starvation. Conversely, a memory grant wait can occur even when the OS has free memory because SQL Server’s query-workspace grant subsystem and workload concurrency impose their own constraints. Chapter 12 covered grants/spills; here the point is to align those internal symptoms with host memory evidence.

4. CPU: distinguish host utilization from SQL scheduler pressure

SQL Server’s SQLOS schedulers expose workers in runnable queues. runnable_tasks_count is the number of workers with tasks assigned that are waiting to get scheduled on a CPU. work_queue_count counts tasks waiting for a worker. Those describe different pressure modes: CPU runnable backlog versus worker-thread scarcity. Sample them repeatedly; a single momentary nonzero value can be normal.

SELECT    SYSDATETIMEOFFSET() AS observed_at,    scheduler_id,    cpu_id,    current_tasks_count,    runnable_tasks_count,    current_workers_count,    active_workers_count,    work_queue_count,    pending_disk_io_countFROM sys.dm_os_schedulersWHERE status = N'VISIBLE ONLINE'ORDER BY scheduler_id;

On Linux, SQL Server 2025 also exposes Linux-specific CPU/VM/disk/network DMVs. Microsoft’s current Linux CPU DMV can be correlated with SQL scheduler queues. On Windows, PerfMon/PowerShell counters provide host CPU/run-queue/storage context. The mandatory course path stops at the SQL DMVs because host tooling differs, but a production incident should include the platform-native layer.

# Run in PowerShell on the SQL Server host when permitted.Get-Counter '\Processor(_Total)\% Processor Time',            '\System\Processor Queue Length',            '\Memory\Available MBytes' |  Select-Object -ExpandProperty CounterSamples |  Select-Object Path, CookedValue, Timestamp

5. Build a correlated resource snapshot

A practical incident packet should timestamp SQL schedulers, process/host memory, file I/O deltas, key performance counters, wait deltas, active requests, Query Store intervals, and host metrics. Align clocks in UTC or with explicit offsets. Then state facts before hypotheses: “scheduler 3 had 12 runnable tasks for four consecutive 5-second samples” is a fact; “we need more CPU” is a hypothesis that still needs query/workload analysis.

Host metrics also need ownership context. On Windows, PerfMon counters may be collected by an infrastructure team; on Linux, tools such as vmstat, iostat, or the SQL Server Linux OS DMVs can provide complementary views. In Kubernetes or other container platforms, capture CPU/memory requests, limits and throttling alongside guest metrics. The diagnostic question is “what resource contract was SQL Server actually running under at this time?” rather than “what hardware model is the host?”

For rates, take paired samples with a monotonic timestamp. For example, a cumulative “Batch Requests/sec” raw value needs two readings; divide the counter delta by elapsed seconds. For I/O, keep read/write counts and stalls separately so a workload-volume change is not mistaken for a device-latency change. For schedulers, keep several consecutive samples because a runnable queue that appears for one instant can be ordinary burstiness.

Authoritative references

• Use SQL Server performance objects
• sys.dm_os_performance_counters
• sys.dm_io_virtual_file_stats
• sys.dm_os_schedulers
• sys.dm_os_process_memory
• sys.dm_os_sys_memory
• Linux CPU statistics DMV