Instance Health in One Query: Uptime, Memory, CPU and Waits

You open a query window because the whole instance feels slow. A quick instance health check should put the main clues in one result set. This query combines uptime, memory, recent CPU, waits, and user sessions without turning one snapshot into a verdict.

Keep the Instance Health Snapshot Honest

I want a compact first view when a problem starts. I also want that view to admit what it does not know. Memory and session counts are current readings. The CPU value comes from a recent scheduler-monitor sample. Wait totals span the period since startup or an explicit reset. Those clocks are different.

Run this on SQL Server for Windows with approved performance visibility. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for the server performance DMVs used here. Earlier releases use VIEW SERVER STATE. This is an instance query, not a portable Azure SQL Database dashboard.

Which part of the server looks different from its normal busy period? That is the useful question. The script does not assign red or green status from arbitrary thresholds. It returns enough context to choose the next investigation. A single row is convenient. It is not a medical certificate for your server.

Build One Instance Health Row Without Multiplying Counts

Each component needs to produce one row before we combine the results. sys.dm_os_sys_info and sys.dm_os_process_memory provide single-row instance information. The session aggregate also returns one row. Ranked waits become three sets of columns through conditional aggregation. OUTER APPLY keeps the main row even when no usable CPU sample exists.

The query excludes a short list of routine idle waits. That list is intentionally visible. Add or remove exclusions only when you understand the wait type. The leading waits are ranked by total wait time, not by current severity.

Notice the collection time and CPU sample age. The XML extraction uses a scheduler-monitor record layout that is an internal diagnostic detail. It is not documented, and it can change between releases. If the record is absent or different, a missing CPU value should stay missing. Never replace it with zero, because zero would claim that the engine was idle. The XML methods also need QUOTED_IDENTIFIER ON. SSMS sets it by default, but sqlcmd does not, so the script sets it first.

SET QUOTED_IDENTIFIER ON;
WITH WaitRank AS
(
    SELECT wait_type, wait_time_ms,
        ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC, wait_type) AS rn
    FROM sys.dm_os_wait_stats
    WHERE wait_time_ms > 0
      AND wait_type NOT IN
        ('SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'LAZYWRITER_SLEEP',
         'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'BROKER_RECEIVE_WAITFOR',
         'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'REQUEST_FOR_DEADLOCK_SEARCH')
), WaitSummary AS
(
    SELECT MAX(CASE WHEN rn = 1 THEN wait_type END) AS TopWait1,
        MAX(CASE WHEN rn = 1 THEN wait_time_ms END) AS TopWait1Ms,
        MAX(CASE WHEN rn = 2 THEN wait_type END) AS TopWait2,
        MAX(CASE WHEN rn = 2 THEN wait_time_ms END) AS TopWait2Ms,
        MAX(CASE WHEN rn = 3 THEN wait_type END) AS TopWait3,
        MAX(CASE WHEN rn = 3 THEN wait_time_ms END) AS TopWait3Ms
    FROM WaitRank
    WHERE rn <= 3
), SessionSummary AS
(
    SELECT COUNT_BIG(*) AS UserSessionCount
    FROM sys.dm_exec_sessions
    WHERE is_user_process = 1
), CpuRecords AS
(
    SELECT [timestamp], TRY_CONVERT(xml, record) AS RecordXml
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
)
SELECT GETDATE() AS CollectedAt, si.sqlserver_start_time AS EngineStartedAt,
    DATEDIFF_BIG(minute, si.sqlserver_start_time, GETDATE()) AS UptimeMinutes,
    si.cpu_count AS LogicalCpuCount,
    si.physical_memory_kb / 1024.0 AS HostPhysicalMemoryMB,
    si.committed_kb / 1024.0 AS EngineCommittedMemoryMB,
    si.committed_target_kb / 1024.0 AS EngineTargetMemoryMB,
    pm.physical_memory_in_use_kb / 1024.0 AS ProcessPhysicalMemoryMB,
    pm.process_physical_memory_low, pm.process_virtual_memory_low,
    cpu.RecordXml.value(
        '(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
        'int') AS SqlCpuPercent,
    cpu.RecordXml.value(
        '(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
        'int') AS SystemIdlePercent,
    (si.ms_ticks - cpu.[timestamp]) / 1000.0 AS CpuSampleAgeSeconds,
    w.TopWait1, w.TopWait1Ms, w.TopWait2, w.TopWait2Ms,
    w.TopWait3, w.TopWait3Ms, s.UserSessionCount
FROM sys.dm_os_sys_info AS si
CROSS JOIN sys.dm_os_process_memory AS pm
CROSS JOIN WaitSummary AS w
CROSS JOIN SessionSummary AS s
OUTER APPLY
(
    SELECT TOP (1) cr.[timestamp], cr.RecordXml
    FROM CpuRecords AS cr
    WHERE cr.RecordXml.exist(
        '/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization') = 1
    ORDER BY cr.[timestamp] DESC
) AS cpu;

Read Memory as Separate Quantities

HostPhysicalMemoryMB describes installed host memory exposed by the DMV. EngineCommittedMemoryMB describes memory committed through the engine’s memory manager. EngineTargetMemoryMB is its current target. ProcessPhysicalMemoryMB describes physical memory used by the SQL Server process. These numbers have different scopes and do not need to match.

Do not subtract one from another and label the result a leak. Engine allocations, process allocations, and operating-system residency are different concepts. The low-memory flags deserve attention when set. They should lead to a closer look at host availability and workload pressure.

I check Windows headroom before treating high SQL Server memory use as a problem. Caching data is part of the engine’s job. High usage alone does not establish pressure. The follow-up query adds available host memory and the operating system’s memory state description. Read it alongside the main snapshot. Also account for other SQL Server instances and services sharing that Windows host.

SELECT total_physical_memory_kb / 1024.0 AS HostTotalMemoryMB,
    available_physical_memory_kb / 1024.0 AS HostAvailableMemoryMB,
    system_memory_state_desc,
    system_high_memory_signal_state,
    system_low_memory_signal_state
FROM sys.dm_os_sys_memory;

Treat CPU as a Sample With an Age

SqlCpuPercent is the utilization value reported for the SQL Server process in the selected scheduler-monitor sample. SystemIdlePercent describes host idle CPU in that same record. These are sampled values, not a live measurement of the request you just started.

CpuSampleAgeSeconds makes a stale sample visible. A recent sample still represents a sampling period rather than an instantaneous reading. A NULL result means the query did not find the expected record. Confirm CPU through Windows Performance Monitor or the SSMS Performance Dashboard before escalating from this field alone.

LogicalCpuCount describes processor availability reported by sys.dm_os_sys_info. It is not CPU utilization. Keep those concepts separate when discussing capacity. If the host is busy but SQL Server’s sample is low, investigate other processes. If SQL Server stays busy across repeated samples, inspect the workload that consumes CPU. One unusual reading should produce another observation, not an immediate instance-wide setting change.

Give Historical Waits the Right Weight

The three wait columns show accumulated worker waiting time. Multiple tasks wait concurrently, so the totals are not elapsed server downtime. EngineStartedAt provides the initial time anchor. An explicit wait-statistics reset shortens that history without changing the engine start time.

For instance health, the useful second step is a pair of snapshots during the complaint. Compare the same wait types across both captures and calculate the increases. If a counter falls, investigate a restart or reset instead of presenting a negative wait rate.

A leading PAGEIOLATCH wait points toward data-page I/O waits, but does not identify the responsible query. RESOURCE_SEMAPHORE directs attention to query execution memory grants. LCK waits direct attention to blocking. These are investigation routes. They are not instructions to add memory, buy storage, or kill a session. Inspect current work and its context before deciding what action makes sense.

Distinguish Connections From Running Work

UserSessionCount includes sleeping application connections as well as sessions doing work. It includes your own monitoring connection. A connection pool can create a large session count without a matching number of running requests. The count is a useful context field, not a concurrency verdict.

Use the next query when the snapshot points toward active workload pressure. It reports user requests and leaves out the monitoring request. Blocking session IDs and current wait information narrow the next question. cpu_time and total_elapsed_time describe the request so far, rather than a lifetime total for the connection.

Do not terminate a session from this result alone. Find the application owner, the open transaction, and the effect of rollback first. A long-running request can be expected batch work. A quiet connection can still hold a transaction that blocks others. Check those relationships before deciding that the most visible row is the source of the complaint.

SELECT r.session_id, r.status, r.command,
    DB_NAME(r.database_id) AS DatabaseName,
    r.cpu_time AS RequestCpuMs,
    r.total_elapsed_time AS RequestElapsedMs,
    r.wait_type, r.wait_time AS CurrentWaitMs,
    r.blocking_session_id
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC, r.session_id;

Use Instance Health to Choose the Next Question

Keep the first output together rather than saving isolated alarming numbers. A memory flag beside available host memory is more useful than a screenshot of one large allocation. A CPU reading with sample age is more useful than a percentage with no timestamp.

Run the same inspection during a known normal period. That comparison gives the fields meaning for your instance. Keep the raw values and collection time so another DBA can repeat your reasoning. If you later turn the query into a regular monitor, retain the distinct time windows and the possibility of missing CPU samples.

The point of instance health is to narrow your attention quickly. Follow memory pressure with memory checks, sustained CPU with request analysis, and blocking with transaction investigation. Confirm the problem in more detailed evidence before changing anything. The single row earns its place by helping you ask a better second question, not by pretending it has already answered every one.

Related reading on this blog: Audit Script to Get CPU and Memory Information with MAXDOP Guidelines and How to Optimize Your Server Performance by Reducing IO Waits?.

A health snapshot is not a diagnosis, it is a map to the next useful check.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.