When users say, “SQL Server is slow”, they’re not talking about wait stats, memory grants, or disk latency. They’re talking about the symptoms of what is causing the server to run slow. As DBAs and architects, our job is to translate those symptoms into evidence, and one of the fastest ways to do that is by using the choosing the right Performance Monitor counters.
You can download my spreadsheet on recommend SQL Server Performance Counters and recommendations here. These are only suggested values. Please test in your own environment as all workloads are different.
This post walks through common real‑world SQL Server performance symptoms, the Performance Monitor counters that matter for each, and where to go next once you see something suspicious. This approach is exactly how I teach performance troubleshooting in workshops:
Symptom: High CPU and Slow Queries
What people notice
- Queries take longer than usual
- CPU is constantly busy
- Performance degrades during peak hours
Key counters to check
- Processor: % Processor Time
- Process(sqlservr): % Processor Time
- SQLServer: SQL Statistics: Batch Requests/sec
What this usually means
- SQL Server is CPU‑bound
- Queries may be expensive, poorly tuned, or running in excessive parallelism
- The workload may simply exceed CPU capacity
Where to go next
- Check waits stats, especially SOS_SCHEDULER_YIELD
- Identify top CPU‑consuming queries
- Review execution plans
- Validate MAXDOP and Cost Threshold for Parallelism
High CPU alone doesn’t tell you why; but it tells you where to start.
Symptom: Queries Waiting on Memory Grants
What people notice
- Queries appear to “hang” before doing any work
- CPU and disk look fine
- Workloads involving sorts or hash joins feel slow
Key counters to check
- SQLServer:Memory Manager: Memory Grants Pending
- SQLServer:Memory Manager: Granted Workspace Memory
What this usually means
- SQL Server doesn’t have enough workspace memory for query execution
- Large sorts, hashes, or spills to tempdb are occurring
Where to go next
- Check waits stats for RESOURCE_SEMAPHORE
- Look for large sort/hash operators in execution plans
- Add or improve indexes
- Reduce memory pressure or increase available memory
- Memory Grants Pending > 0 is always actionable.
Symptom: Disk Latency or Slow I/O
What people notice
- Queries stall on reads or writes
- Backups, restores, or index maintenance take too long
- Overall system feels sluggish even with low CPU
Key counters to check
- Physical Disk: Avg. Disk sec/Read
- Physical Disk: Avg. Disk sec/Write
- Physical Disk: Disk Reads/sec
- Physical Disk: Disk Writes/sec
What this usually means
- Storage latency or bandwidth constraint
- Tempdb or transaction log is often involved
Where to go next
- Validate file‑level latency using sys.dm_io_virtual_file_stats
- Check tempdb and log file placement
- Confirm storage configuration and IO limits
- Separate data and log workloads if needed
- Trust latency counters more than disk queue length
Symptom: Memory Pressure in SQL Server
What people notice
- Cache churn
- Frequent physical reads
- Performance degrades as workload grows
Key counters to check
- SQLServer: Buffer Manager: Free List Stalls/sec
- SQLServer: Buffer Manager: Lazy Writes/sec
- SQLServer: Buffer Manager: Page Life Expectancy
What this usually means
- SQL Server is under memory pressure
- The buffer pool is constantly being recycled
Where to go next
- Tune queries and indexes to reduce memory footprint
- Validate max server memory configuration
- Reduce competing workloads or add RAM
- Monitor PLE at the NUMA node level
Symptom: Blocking and Concurrency Problems
What people notice
- Queries wait even when CPU and disk are idle
- Sessions appear “stuck”
- Performance issues come and go unpredictably
Key counters to check
- SQLServer: Locks: Lock Waits/sec
- SQLServer: Locks: Avg Wait Time (ms)
- SQLServer: General Statistics: Processes Blocked
What this usually means
- Blocking caused by long‑running or poorly designed transactions
- Lock escalation or isolation issues
Where to go next
- Identify the blocking session
- Look for long open transactions
- Review isolation levels and access patterns
- Consider indexing or query changes
Symptom: Excessive Recompiles
What people notice
- CPU spikes unexpectedly
- Plans don’t seem to stick
- Performance is inconsistent
Key counters to check
- SQLServer:SQL Statistics : SQL Recompilations/sec
What this usually means
- Plans are being invalidated due to:
- Statistics changes
- Schema modifications
- Temp tables or table variables
Where to go next
- Review query patterns
- Evaluate temp object usage
- Stabilize schema where possible
- Recompiles should be rare, not routine.
Symptom: TempDB Pressure
What people notice
- TempDB grows rapidly
- Hash joins and sorts get slow
- Workloads degrade as concurrency increases
Key counters to check
- SQLServer: Access Methods: Workfiles Created/sec
- SQLServer: Access Method: Worktables Created/sec
What this usually means
- Queries are spilling to tempdb
- Excessive hash or sort operations
Where to go next
- Tune queries and indexes
- Improve TempDB configuration
- Reduce unnecessary sorts or spools
- TempDB is often the canary in the coal mine for poor query design.
Symptom: Transaction Log Write Bottleneck
What people notice
- Simple INSERT/UPDATE operations are slow
- Commit operations take longer than expected
- Performance stalls during peak write activity
Key counters to check
- SQLServer: Databases: Log Flush Waits/sec
- SQLServer: Databases: Log Flush Wait Time
What this usually means
- Log write latency
- Storage can’t keep up with commit rate
Where to go next
- Move log files to faster storage
- Batch transactions
- Avoid unnecessary commits
- If the log can’t keep up, everything waits
Useful Counter Targets and Guidance
These are only suggested values. Please test in your own environment as all workloads are different. You can download the excel file with more details here.
| Object | Counter | Target / Guidance | Notes / Interpretation |
|---|---|---|---|
| Logical Disk | % Free Space | > 15% | Free space on logical volume |
| Physical Disk | % Idle Time | > 20% | Low values indicate disk saturation |
| Physical Disk | Avg. Disk sec/Read | < 8–25 ms | Primary read latency metric |
| Physical Disk | Avg. Disk sec/Write | < 4–25 ms | Primary write latency metric; logs require lowest |
| Physical Disk | Avg. Disk Queue Length | < 2–3 × spindles | Heuristic only; less useful on SAN/NVMe |
| Physical Disk | Disk Reads/sec | < 15 ms | High Disk Reads/sec is not bad by itself High Disk Reads/sec with high latency is bad |
| Physical Disk | Disk Writes/sec | < 10–15 ms | Write IOPS |
| Physical Disk | Split IO/sec | Low | Fragmentation indicator |
| Processor | % Processor Time | < 80% sustained | Overall CPU utilization |
| Processor | % Privileged Time | < 30% | High values may indicate driver/kernel issues |
| Processor | % Interrupt Time | < 15% | Hardware interrupt pressure |
| System | Processor Queue Length | < 4 per CPU | Runnable threads waiting for CPU |
| System | Context Switches/sec | Baseline | High values indicate scheduling pressure |
| Memory | Available MBytes | > 150 MB | For modern SQL Server hosts: < 500 MB = memory pressure < 200 MB = severe pressure |
| Memory | % Committed Bytes In Use | < 80% | System-wide commit pressure |
| Memory | Pages Input/sec | < 10 | Hard page faults from disk |
| Memory | Pages/sec | < 50 | Disk-backed paging activity |
| Paging File | % Usage | < 70% | Excessive paging indicates memory pressure |
| Paging File | % Usage Peak | < 70% | Peak paging since boot |
| Network Interface | Bytes Total/sec | < 70% of NIC | Network saturation indicator |
| Network Interface | Output Queue Length | < 2 | Queued outbound packets |
| Process (sqlservr) | % Processor Time | < 80% | CPU consumed by SQL Server |
| Process (sqlservr) | Thread Count | Baseline < 500 | Thread pressure / leaks |
| Process (sqlservr) | Working Set | Trend | Memory in use by SQL Server |
| SQLServer:SQL Statistics | Batch Requests/sec | Trend | Primary workload throughput indicator |
| SQLServer:SQL Statistics | SQL Compilations/sec | < 10% of Batch Requests/sec | Ad-hoc / plan reuse issues |
| SQLServer:SQL Statistics | SQL Recompilations/sec | Near 0 | Schema or stats churn |
| SQLServer:Access Methods | Full Scans/sec | ~1 per 1000 index searches | Missing or unused indexes |
| SQLServer:Access Methods | Page Splits/sec | < 20 per 100 Batch Req/sec | Index fragmentation pressure |
| SQLServer:Access Methods | Workfiles Created/sec | < 20 | Hash/sort spills to tempdb |
| SQLServer:Access Methods | Worktables Created/sec | < 20 | Spools/temp objects |
| SQLServer:Buffer Manager | Page Life Expectancy | > 300 (trend) | Buffer pool churn indicator |
| SQLServer:Buffer Manager | Free List Stalls/sec | < 2 | True memory pressure indicator |
| SQLServer:Buffer Manager | Lazy Writes/sec | < 20 | Buffer pressure |
| SQLServer:Buffer Manager | Page Reads/sec | < 90 | Physical reads/sec |
| SQLServer:Buffer Manager | Page Writes/sec | < 90 | Physical writes/sec |
| SQLServer:Memory Manager | Memory Grants Pending | 0 | RESOURCE_SEMAPHORE pressure |
| SQLServer:Memory Manager | Total Server Memory (KB) | ≈ Target | SQL memory steady state |
| SQLServer:Memory Manager | Target Server Memory (KB) | Configured max | Max SQL memory |
| SQLServer:Databases | Log Flush Waits/sec | ≈ 0 | Transaction log latency |
| SQLServer:Databases | Percent Log Used | < 80% | Avoid log full stalls |
| SQLServer:Databases | Log Growths | 0 during workload | Autogrowth stalls |
| SQLServer:Locks | Lock Waits/sec | 0 | Blocking indicator |
| SQLServer:Locks | Number of Deadlocks/sec | < 1 | Concurrency defects |
| SQLServer:Latches | Latch Waits/sec | Trend | In-memory contention |
| SQLServer:Wait Statistics | Wait Time / sec | Trend | High-level SQL waiting categories |
Final Thoughts
Performance Monitor becomes far more useful when you stop staring at all the counters and instead focus on the ones that map to real symptoms.
When troubleshooting SQL Server:
- Start with what users experience
- Capture the few counters that matter
- Correlate with wait stats, DMVs, and execution plans
Be the first to comment on "Choosing the Right Performance Counters"