SQL Server Performance Counters – Monitoring Best Practices

Overview

Performance counters provide real‑time insight into SQL Server health and workload characteristics. Following best‑practice guidelines ensures that you capture useful data without overwhelming the system.

Collect Data Effectively

Use PerfMon or sys.dm_os_performance_counters for on‑premises instances.
For Azure SQL Database, rely on sys.dm_db_resource_stats and Azure Monitor.
Sample at intervals of 15‑30 seconds for short‑term troubleshooting, and 5‑15 minutes for long‑term trend analysis.

SELECT 
    counter_name,
    cntr_value,
    cntr_type,
    object_name,
    instance_name
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQLServer:Buffer Manager%';

Establish Baselines

Baseline values differ per workload. Capture at least 7 days of data during normal operation. Store in a dedicated monitoring database.

CREATE TABLE dbo.PerfCounterLog (
    LogTime DATETIME2 NOT NULL,
    CounterName NVARCHAR(128),
    InstanceName NVARCHAR(128),
    CounterValue BIGINT
);

Alerting & Thresholds

Define alerts on both absolute values and rate of change.

IF EXISTS (
    SELECT 1 FROM dbo.PerfCounterLog
    WHERE CounterName = 'Page life expectancy'
      AND CounterValue < 300
      AND LogTime > DATEADD(MINUTE,-5,GETUTCDATE())
)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name='DBA Alerts',
        @recipients='dba@example.com',
        @subject='Low PLE Alert',
        @body='Page life expectancy fell below 300.';
END

Visualization

Leverage Power BI, Grafana, or Azure Dashboard to display trends.

Automation Scripts

Use PowerShell to schedule regular snapshots.

param(
    [string]$Instance = 'localhost',
    [int]$IntervalSec = 30,
    [int]$DurationMin = 60
)

$end = (Get-Date).AddMinutes($DurationMin)
while ((Get-Date) -lt $end) {
    $counters = Get-Counter -ComputerName $Instance -Counter '\SQLServer:*'
    $counters.CounterSamples | ForEach-Object {
        $obj = [pscustomobject]@{
            Time = Get-Date
            Counter = $_.Path
            Value = $_.CookedValue
        }
        $obj | Export-Csv -Path 'C:\PerfLogs\snapshot.csv' -Append -NoTypeInformation
    }
    Start-Sleep -Seconds $IntervalSec
}

SQL Server Documentation