SQL Server performance counters to monitor
Perfmon counters and DMVs to monitor the SQL Server engine, SQL Server Agent, Analysis Services, Reporting Services and Service Broker.
Performance Monitor counters and DMVs that help monitor a SQL Server instance, grouped by component. Thresholds are those of the reference this article is based on; the other counters are listed without comment.
Database engine
| Counter | Description | Threshold or remark |
|---|---|---|
SQLServer:Buffer Manager\Buffer cache hit ratio |
Share of page reads served from the buffer cache; shows whether there is enough memory | Should stay above 97 % |
SQLServer:Access Methods\Page Splits/sec |
Page splits | If they cause a performance problem: increase the fill factor or rebuild the indexes |
SQLServer:General Statistics\User Connections |
Number of connections (and users) on the server | - |
SQLServer:Memory Manager\Total Server Memory (KB) |
Memory actually used by SQL Server | - |
SQLServer:SQL Errors\Errors/sec |
Errors per second | - |
SQLServer:Locks\Number of Deadlocks/sec |
Deadlocks per second | - |
SQLServer:SQL Statistics\SQL Compilations/sec |
Compilations per second | < 2 negligible; 2 to 20 to investigate; 20 to 100 poor; > 100 potentially serious |
Counters without a fixed threshold are used to spot a trend against a baseline for the instance.
Other counters to track, with no description in the reference:
- Access Methods: Full Scans/sec, Table Lock Escalations/sec, Worktables Created/sec, Workfiles Created/sec
- Buffer Manager: Database pages, Free list stalls/sec, Page life expectancy, Page reads/sec, Page writes/sec, Total pages
- Databases: Log Growths, Percent Log Used, Processes Blocked, Transactions/sec (tempdb)
- Locks: Average Wait Time (ms)
- Plan cache: Cache Object Counts
- SQL Statistics: Batch Requests/sec, SQL Re-Compilations/sec
- Transactions (tempdb): Free space in tempdb (KB), Version Store (KB), Version Generation Rate (KB), Version Cleanup Rate
- General Statistics: Temp Tables Creation Rate
SQL Server Agent
| Counter | Description | Frequency |
|---|---|---|
SQLAgent:Jobs\Failed Jobs |
Failed jobs | Daily |
SQLAgent:Jobs\Job success rate |
Job success rate | Daily |
Analysis Services
Collection frequency: hourly, for all the counters below.
| Counter | Description |
|---|---|
MSAS:Cache\Total Direct Hits |
Total direct cache hits: queries answered from existing cache entries |
MSAS:Connections\Requests/sec |
Rate of connection requests |
MSAS:Connections\Failures/sec |
Rate of connection failures |
MSAS:Connections\Total Failures |
Total failed connection attempts |
MSAS:Locks\Current Locks |
Number of objects currently locked |
MSAS:Locks\Total Deadlocks Detected |
Total deadlocks detected |
MSAS:Memory\Filestore IO Errors |
Total filestore I/O errors |
MSAS:Processing\Total Rows Read |
Rows read from all relational databases |
MSAS:Processing\Total Rows Written |
Rows written during processing |
MSAS:Storage Engine Query\Aggregation hits/sec |
Rate of aggregation hits |
MSAS:Storage Engine Query\Avg time/query |
Average time per query, in milliseconds |
Reporting Services
| Counter | Description | Type or frequency |
|---|---|---|
ReportServer:Service\Request Rejected |
Requests not executed because of insufficient resources; they return HTTP 503 (server too busy) | Alert on occurrence |
ReportServer:Service\Memory Pressure State |
Memory pressure state from 1 (no pressure) to 5 (exceeded): 1 none, 2 low, 3 medium, 4 high, 5 exceeded | Daily |
ReportServer:Service\Request Disconnected |
Requests disconnected because of a communication failure | Daily |
ReportServer:Webservice\Requests/sec |
Requests per second | Daily |
ReportServer:Webservice\Reports Executed/sec |
Successful report executions per second; compare with the request rate to see how many were served from the cache | Daily |
| Reports performance monitoring | Execution time of each report, collected from the report server database | Weekly |
Service Broker
Counters (hourly):
MSSQL:Broker Activation\Stored Procedure Invoked/sec: stored procedures invoked per secondMSSQL:Broker Activation\Task Started/sec: activated tasks started per secondMSSQL:Broker Activation\Task Aborted/sec: activated tasks aborted per secondMSSQL:Broker Statistics\Activation Errors Total: number of times an activation stored procedure exited with an errorMSSQL:Broker Statistics\Corrupted Messages Total: number of corrupted messages received by the instanceMSSQL:Broker Statistics\SQL Receives/sec: RECEIVE statements processed per second
Service Broker DMVs (hourly):
sys.dm_broker_activated_tasks: one row per stored procedure activated by Service Brokersys.dm_broker_connections: one row per Service Broker network connectionsys.dm_broker_forwarded_messages: one row per message the instance is forwardingsys.dm_broker_queue_monitors: one row per queue monitor; a queue monitor manages activation for a queue
System counters
Network Interface\Current BandwidthandNetwork Interface\Bytes Total/secPhysicalDisk\Split IO/secSystem\Context Switches/secandSystem\Processor Queue LengthProcess\% Processor TimeandProcess\% Privileged Time
Reading counters with PowerShell
Get-Counter -ListSet * | ft
Get-Counter -Counter @('\Memory\Available MBytes') -SampleInterval 2 -MaxSamples 10
Get-Counter -ListSet SQLServer:Buffer* | foreach {$_.Paths}
Get-Counter -Counter @('\SQLServer:Buffer Manager\Page life expectancy') -SampleInterval 2 -MaxSamples 10