Performance Counters
Performance counters provide real‑time metrics that help you monitor the health and performance of SQL Server. These counters can be viewed in Windows Performance Monitor (PerfMon) or accessed programmatically via DMVs.
Categories
Key Counters
| Counter | Category | Instance | Description | Typical Values |
|---|---|---|---|---|
| Buffer Cache Hit Ratio | SQLServer:Buffer Manager | _Total | Percentage of pages found in the buffer cache without having to read from disk. | > 90% |
| SQL Compilations/sec | SQLServer:SQL Statistics | _Total | Number of SQL statements compiled per second. | < 5 |
| Page Life Expectancy | SQLServer:Buffer Manager | _Total | How long a page stays in the buffer pool (seconds). | > 300 |
| Log Flushes/sec | SQLServer:Databases | <database_name> | Number of transaction log flushes per second. | Varies |
| Batch Requests/sec | SQLServer:SQL Statistics | _Total | Number of command batches received per second. | Depends on workload |
Monitoring Tips
- Use PerfMon custom data collector sets for long‑term trends.
- Combine counters with DMVs such as
sys.dm_os_performance_countersfor granular analysis. - Set alerts on critical counters (e.g., low Page Life Expectancy).
- Correlate counter spikes with query plans using Query Store.