LLI Wiki — My Digital Knowledge Base

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:

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):

Service Broker DMVs (hourly):

System counters

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
Mastodon LLIMastodon SocialFosstodon