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 ways to do that is by 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.
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 limited by CPU Pressure.
- 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
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"