sys.dm_os_performance_counters - Usage & Examples

SQL Server maintains an internal performance counter infrastructure that mirrors Windows Performance Monitor counters. The SQLOS layer updates these counters continuously as operations complete within the database engine. Each counter has a specific counter_type that determines how values are calculated and interpreted.

The buffer manager updates CNTR_VALUE counters like "Buffer cache hit ratio" by tracking page reads from disk versus memory. The lock manager increments "Number of Deadlocks/sec" counters each time deadlock detection resolves conflicts. The SQL Statistics object tracks "Batch Requests/sec" by incrementing counters in the connection layer as T-SQL batches execute.

Counter types fall into three categories: instantaneous values (PERF_COUNTER_RAWCOUNT), rates per second (PERF_COUNTER_COUNTER), and ratios (PERF_COUNTER_LARGE_RAWCOUNT with base counters). Ratio counters require mathematical calculation using both the numerator and denominator values exposed in separate rows.

SQL Server 2016 introduced additional Always On and In-Memory OLTP counters. SQL Server 2019 added Intelligent Query Processing counters. SQL Server 2022 expanded counters for Ledger and Purview integration. The counter structure remained consistent across versions, but new objects and counters appear as features are added.

Memory pressure affects counter accuracy because the performance counter infrastructure competes with other engine components for memory allocation. During severe memory pressure, some counters may temporarily report zero or incorrect values until memory stabilizes.

-- Check buffer cache hit ratio and related memory counters
SELECT 
    object_name,
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
    AND counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base', 
                        'Page life expectancy', 'Lazy writes/sec')
ORDER BY counter_name;

-- Calculate actual buffer cache hit ratio percentage
SELECT 
    (SELECT cntr_value FROM sys.dm_os_performance_counters 
     WHERE counter_name = 'Buffer cache hit ratio') * 100.0 /
    (SELECT cntr_value FROM sys.dm_os_performance_counters 
     WHERE counter_name = 'Buffer cache hit ratio base') AS buffer_cache_hit_ratio_pct;

-- Monitor transaction and locking activity
SELECT 
    object_name,
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Transactions%'
    OR (object_name LIKE '%Locks%' AND counter_name LIKE '%Deadlocks%')
    OR counter_name LIKE '%Lock Waits%'
ORDER BY object_name, counter_name;

-- Track SQL compilation and recompilation rates
SELECT 
    counter_name,
    cntr_value,
    CASE 
        WHEN cntr_type = 272696576 THEN 'Per Second Counter'
        WHEN cntr_type = 65792 THEN 'Instantaneous Value'
        ELSE 'Other'
    END AS counter_type_desc
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQL Statistics%'
    AND counter_name IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec', 
                        'Batch Requests/sec')
ORDER BY counter_name;

-- Track server-level page split activity (Access Methods counter is instance-wide, not per-database)
SELECT 
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Access Methods%'
    AND counter_name = 'Page Splits/sec';

Reset specific performance counters (Emergency use only) This script forces SQL Server to reinitialize performance counter values, but requires instance restart to take full effect.

-- WARNING: This requires SQL Server restart and should only be used 
-- when counters are clearly corrupted
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC DROPCLEANBUFFERS;
-- Restart SQL Server service after running these commands

Caveats: Only use when counters show impossible values like negative buffer cache hit ratios. Test in development first. This clears all cached plans and data pages.

Create monitoring baseline for critical counters Establishes historical tracking table for performance counter trends and automated alerting.

-- Create performance counter baseline table
CREATE TABLE dbo.performance_counter_baseline (
    capture_time DATETIME2(3) DEFAULT GETDATE(),
    object_name NVARCHAR(128),
    counter_name NVARCHAR(128),
    instance_name NVARCHAR(128),
    cntr_value BIGINT,
    cntr_type INT
);

-- Populate with current values for key counters
INSERT INTO dbo.performance_counter_baseline (object_name, counter_name, instance_name, cntr_value, cntr_type)
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Buffer cache hit ratio', 'Page life expectancy', 'Batch Requests/sec',
                      'SQL Compilations/sec', 'Page Splits/sec', 'Lock Waits/sec');

Expected Impact: Creates foundation for trending analysis and threshold alerting. Run hourly via SQL Agent job.

Fix corrupted ratio counter calculations Recalculates percentage-based counters when base values become inconsistent.

-- Verify and fix buffer cache hit ratio calculation
WITH ratio_fix AS (
    SELECT 
        MAX(CASE WHEN counter_name = 'Buffer cache hit ratio' THEN cntr_value END) AS numerator,
        MAX(CASE WHEN counter_name = 'Buffer cache hit ratio base' THEN cntr_value END) AS denominator
    FROM sys.dm_os_performance_counters
    WHERE counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base')
)
SELECT 
    numerator,
    denominator,
    CASE 
        WHEN denominator = 0 OR denominator IS NULL THEN 0
        ELSE (numerator * 100.0) / denominator 
    END AS corrected_hit_ratio_pct
FROM ratio_fix;

-- If ratio shows > 100% or negative values, restart SQL Server
-- This indicates internal counter corruption requiring service restart

Expected Impact: Identifies when performance counter infrastructure needs restart to resolve corruption.

Configure SQL Server Agent jobs to capture sys.dm_os_performance_counters data every 15 minutes into permanent tables. Establish baseline ranges for critical counters like buffer cache hit ratio (>95%), page life expectancy (>300 seconds), and compilation ratios (<10% recompiles to compilations).

Set up automated alerts when buffer cache hit ratio drops below 90%, page life expectancy falls under 300 seconds, or deadlocks per second exceed your application tolerance. Monitor server-level Page Splits/sec (an Access Methods counter, not a per-database metric) to identify indexing problems before they impact performance.

Use Performance Monitor (perfmon) to validate DMV counter accuracy during high-load periods. Create custom counter sets that include SQL Server-specific counters alongside Windows system counters for correlation analysis.

Implement memory configuration best practices to prevent counter corruption: set max server memory to appropriate values, enable lock pages in memory for service accounts, and avoid auto-shrink settings that cause memory pressure spikes.

Schedule regular CHECKDB operations during maintenance windows to verify database integrity, as corruption can affect internal counter accuracy. Configure trace flags 3226 and 1118 to reduce transaction log noise and tempdb contention that can skew performance counter readings.

Establish counter trending reports that identify gradual degradation patterns before they become critical issues. Focus on ratios and rates rather than absolute values for meaningful performance analysis across different workload periods.