Useful SQL Server monitoring connects a few measurements to the work users are doing. A single red counter cannot explain whether the server is overloaded, waiting, recovering, or simply busy with expected work.
Start With Activity and User Impact
Establish whether requests are arriving and whether the important operation is succeeding. Low activity can mean a quiet period or an application that cannot connect. High activity can mean productive work or repeated failing retries.
Record timestamps and instance startup time with samples. Counters that reset cannot be compared blindly across a restart. Keep the collection interval visible so a short burst is not confused with a sustained trend.
SELECT SYSUTCDATETIME() AS collected_at_utc,
sqlserver_start_time
FROM sys.dm_os_sys_info;
SELECT object_name, counter_name, instance_name,
cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Batch Requests/sec';The DMV exposes raw counter values. For rate counters, calculate the difference between samples over elapsed time using the documented counter type. Do not display the cumulative value as if it were already the current per-second rate.
Use Wait Deltas to Find the Dominant Constraint
Wait statistics can show where work spent time waiting during an interval. Capture two samples and compare their differences. Lifetime totals may be dominated by activity unrelated to today's complaint.
SELECT wait_type, waiting_tasks_count,
wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0;Separate expected background waits from workload-related evidence. A wait category points toward an investigation, not a guaranteed root cause. Storage waits, for example, can reflect excessive reads as well as slow storage.
Do not clear shared wait statistics just to simplify one dashboard. Store samples and handle resets explicitly. Also remember that concurrent tasks can accumulate wait time faster than wall-clock time.
Read Page Life Expectancy in Context
Page life expectancy is a buffer-pool residency indicator measured in seconds. A drop can suggest churn, but its significance depends on workload, memory, and the surrounding evidence. There is no useful universal alarm threshold for every server.
SELECT object_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy'
ORDER BY object_name, instance_name;On NUMA systems, inspect Buffer Node values as well as the overall Buffer Manager view. An aggregate can hide a problem concentrated on one node. Compare a drop with reads, active work, and memory-pressure signals.
An expected large scan can disturb the cache without proving that more memory is the only answer. Investigate access paths and workload timing too. Buying memory should be a conclusion supported by evidence, not a reflex to one dip.
Check Memory Pressure Directly
SQL Server using much of its configured memory is not automatically a fault. The engine caches data to avoid repeated storage reads. Look for pressure and its consequences rather than treating a large allocation as an error.
SELECT available_physical_memory_kb, system_memory_state_desc
FROM sys.dm_os_sys_memory;
SELECT physical_memory_in_use_kb,
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory;
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Memory Grants Pending';Pending memory grants describe requests waiting for execution memory. Correlate sustained waits with the relevant queries and resource use. A momentary value during a sample is not a complete workload diagnosis.
Measure File Latency Over the Same Interval
Virtual file statistics expose cumulative I/O counts and stall time by file. Use differences between samples to calculate interval averages. Keep reads and writes separate because their workload and latency can differ.
SELECT database_id, file_id, num_of_reads,
io_stall_read_ms, num_of_writes, io_stall_write_ms,
num_of_bytes_read, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL);Divide stall-time change by operation-count change only when the denominator is positive. Handle resets instead of producing negative latency. Preserve per-file detail before summarizing a database or instance.
High latency with little activity can deserve a different investigation from high latency during intense maintenance. Pair the file measurements with waits and active requests. Averages can also hide individual slow operations.
Build a Small Story Instead of a Large Wall
Arrange the evidence around activity, waiting, memory pressure, and storage behavior for the same time window. Add deployment and maintenance markers. The dashboard should help explain what changed.
Alert on conditions that have an owner and a useful response. Keep lower-priority trends available for review without waking someone for every fluctuation. A handful of understood counters beats a wall of unexplained numbers.
Monitoring is not collecting every number, it is connecting evidence to a useful decision.
This post was rewritten from scratch in September 2026. The original, published on 2019-04-24, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
