SQL Server performance counters can look absurd when a cumulative value is read as a rate. Batch Requests/sec can display a number in the millions, while Buffer cache hit ratio appears as a bare integer. The counter type tells you whether to subtract two samples or divide by a base.
Inspect Performance Counters by Type Before Value
sys.dm_os_performance_counters exposes object_name, counter_name, instance_name, cntr_value, and cntr_type. The names resemble Windows Performance Monitor labels, but the raw DMV values are not always ready-to-display numbers. A per-second counter can be cumulative since engine start. A ratio counter needs its base. A snapshot counter is already a current reading. Read cntr_type before putting a raw number on a dashboard.
I have seen a seven-digit Batch Requests/sec value interpreted as seven million requests in the last second. It was a long-running cumulative counter. What question does the chart answer if its unit is wrong? Write the conversion beside the number, then test it against an independent workload observation.
Map the Common Counter Types
Microsoft documents 65792 as a snapshot value. Types 272696320 and 272696576 need two observations to calculate a per-second rate. Type 537003264 is a ratio numerator, with a corresponding base counter of type 1073939712. Type 1073874176 is another average form with a base. Do not generalize one formula to every counter that ends with /sec or has a ratio in its name.
SELECT object_name, counter_name, instance_name,
cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name IN
(
N'Batch Requests/sec', N'SQL Compilations/sec',
N'Page life expectancy', N'Memory Grants Pending',
N'Buffer cache hit ratio', N'Buffer cache hit ratio base'
)
ORDER BY object_name, counter_name, instance_name;Object names include the instance prefix, so filtering only on counter_name is convenient for discovery. A server with multiple objects that use a similar label needs an exact object and instance match in a production collector. Permissions differ by version; current SQL Server versions require server performance state access for this DMV.
Turn Two Samples Into a Rate
Take two values from the same counter and instance ten seconds apart. Divide the difference in values by the actual elapsed seconds. WAITFOR is a simple teaching tool; a production collector should timestamp its samples and survive restarts. Use DATEDIFF with enough precision that scheduling delay does not become a hidden error.
DECLARE @sample TABLE
(
counter_name nvarchar(128),
instance_name nvarchar(128),
cntr_value bigint,
sampled_at datetime2(3)
);
INSERT @sample
SELECT counter_name, instance_name, cntr_value, SYSUTCDATETIME()
FROM sys.dm_os_performance_counters
WHERE counter_name IN
(N'Batch Requests/sec', N'SQL Compilations/sec');
WAITFOR DELAY '00:00:10';
SELECT e.counter_name, e.instance_name,
(e.cntr_value - s.cntr_value) * 1000.0 /
NULLIF(DATEDIFF_BIG(millisecond, s.sampled_at,
SYSUTCDATETIME()), 0) AS per_second
FROM @sample AS s
JOIN sys.dm_os_performance_counters AS e
ON e.counter_name = s.counter_name
AND e.instance_name = s.instance_name
WHERE e.cntr_value >= s.cntr_value;The sample timestamp is captured per row, while the final clock is read during the SELECT. For a precise collector, capture one timestamp per snapshot and retain object_name too. The demonstration is enough to show why a raw cumulative value must be converted. A lower second value indicates reset or restart; discard that interval rather than reporting a negative rate.
Divide a Ratio by Its Matching Base
Buffer cache hit ratio is a numerator. Divide it by Buffer cache hit ratio base and multiply by 100. Match the same object and instance. If the base is zero, report no value instead of a fabricated percentage. A lifetime ratio can hide a recent change, so a second sample and a delta ratio can be useful for a short interval.
SELECT n.object_name, n.instance_name,
n.cntr_value * 100.0 / NULLIF(b.cntr_value, 0)
AS buffer_cache_hit_percent
FROM sys.dm_os_performance_counters AS n
JOIN sys.dm_os_performance_counters AS b
ON b.object_name = n.object_name
AND b.instance_name = n.instance_name
AND b.counter_name = N'Buffer cache hit ratio base'
WHERE n.counter_name = N'Buffer cache hit ratio';The percentage is not a universal performance score. A high lifetime ratio can coexist with bad queries or a new I/O problem. A low ratio can follow a restart or a workload that streams through large data. Interpret it with reads, memory pressure, and user latency rather than treating one number as a verdict.
Add Page Life and Grant Pressure
Page life expectancy is a snapshot of how long pages stayed in the buffer pool by the counter's definition. Memory Grants Pending is a snapshot count of requests waiting for a workspace memory grant. Read them directly, with object and instance names. On NUMA systems, page life expectancy can appear per buffer node and as an aggregate; label the instance you display.
SELECT object_name, counter_name, instance_name,
cntr_value AS current_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN
(N'Page life expectancy', N'Memory Grants Pending')
AND cntr_type = 65792;A single low page life number is not proof that RAM must be added. Watch trend, workload, memory grants, cache churn, and waits. A nonzero pending grant count during sustained query delays is a stronger lead than a one-second spike. Record the collection interval so the sample can be compared with a user report.
Build a Short Script for Performance Counters
For a compact dashboard, put Batch Requests/sec and SQL Compilations/sec through the two-sample calculation, and Page life expectancy and Memory Grants Pending through the snapshot query. Add Buffer cache hit ratio only with its base. The three snippets above are a short runnable set for that purpose. Save both raw samples with timestamps so the displayed numbers can be audited later.
I compare compilation rate with batch rate as a proportion, not as two unrelated totals. A high compilation share can lead to plan-cache or parameterization review, but it needs query evidence. I also check the engine start time before comparing samples from different collections. The point of performance counters is to give a useful lead; the type and time window keep that lead honest.
Keep Instances of Performance Counters Straight
The same counter name can appear under several objects or instances. A collector that joins only on counter_name can divide a ratio numerator by the wrong base. Use object_name and instance_name as part of the identity, and inspect them after an instance rename or upgrade. For a named SQL Server instance, the object prefix differs from a default instance. Hard-coded full object labels can therefore make a script return no rows after a move.
Handle Restarts and Missing Data
A counter reset breaks a two-sample calculation. Compare sys.dm_os_sys_info.sqlserver_start_time with the previous collection and drop the interval if the engine restarted. Also report an unavailable counter as missing rather than zero. Zero says the measured activity was absent; missing says the system did not supply a trustworthy reading. That distinction matters in an alert.
I keep the raw values, collection times, calculated rate, and formula in a small table. When an alert says compilations jumped, the DBA can recheck the arithmetic and see whether the sample straddled a restart. A ten-second teaching script is useful; a persistent monitor also needs collection health, retention, and a clear unit for every field.
Related reading on this blog: The Handful of Counters That Actually Matter and Identify Read Heavy Workload or Write Heavy Workload Type by Counters.
A counter label is not the calculated result, it is a value whose type defines the math.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
