Hello Guys,

We all are aware that monitoring a database server for that matter any server is of prime importance in order to ensure business continuity. I will be talking only from database perspective and to be very precise SQL server.

As any DBA I am also responsible to ensure uninterrupted availability of database servers. Monitoring is a vast topic. There are many parameters and enormous counter available in Performance monitor.

Well all of the counters can be monitored however, it will provide us with lot of data points leading to loads of confusion and too much data generally leads no where.

I have came up with list of few but important counters with respect to different SQL Server Services that can be monitored using performance monitor. These counters will surely help DBA’s predict the probable crisis in advance.

P.S: Threshold and Frequency can be configured as per needs.

SQL SERVER DATABASE ENGINE

 CounterSourceDescriptionTypeThresholdFrequency
SQLServer: Access Methods — Page Splits/secPerfmonif page splits are causing performance issue.
Increase fill factor or rebuild indexes
Pattern basedNAHourly
SQLServer: Buffer Manager — Buffer Cache Hit RatioPerfmonTo find out if there is enough memory in the serverPattern basedNAHourly
SQLServer: General Statistics — User ConnectionsPerfmonTo see how many connections (and users) are using the serverPattern basedNAHourly
SQLServer: Memory Manager — Total Server Memory (KB)PerfmonHow much memory SQL server is actually usingPattern basedNAHourly
SQLServer:Errors- Errors/SecPerfmonNumber of errors per secondPattern basedNAHourly
SQLServer:Locks- Number Of deadlocks/SecPerfmonNumber of deadlocksPattern basedNAHourly
SQLServer:SQL Statistics- SQL Compilations/SecPerfmonCompiles per secondPattern based<2/sec negligible,
2-20/sec could be investigated,
20-100 poor,
>100 potentially serious
Hourly

SQL SERVER AGENT

CounterSourceDescriptionTypeThresholdFrequency
SQLAgent:Jobs-Failed JobsPerfmonFailed JobsPattern basedNADaily
SQLAgent:Jobs-Job success ratePerfmonJob success ratePattern basedNADaily

SQL SERVER INTEGRATION SERVICES

CounterSourceDescriptionTypeThresholdFrequency
SQL Server:SSIS Service- SSIS package InstancesPerfmonThe number of package instances currently running.Pattern NAHourly

SQL SERVER ANALYSIS SERVICES

CounterSourceDescriptionTypeThresholdFrequency
MSAS:Cache-Total Direct HitsPerfmonTotal count of direct cache hits.  Queries were answered from existing cache entries.Pattern NAHourly
MSAS:Connections-Failures/SecPerfmonRate of connection failures.Pattern NAHourly
MSAS:Connections-Requests/SecPerfmonRate of connection requests.  These are arrivals.Pattern NAHourly
MSAS:Connections-Total FailuresPerfmonTotal failed connection attempts.Pattern NAHourly
MSAS:Locks-Current LocksPerfmonCurrent number of locked objects.Pattern NAHourly
MSAS:Locks-Total Deadlocks DectedPerfmonTotal number of deadlocks detected.Pattern NAHourly
MSAS:Memory-Filestore IO ErrorsPerfmonFilestore IO Errors total.Pattern NAHourly
MSAS:Processing-Total Rows readPerfmonCount of rows read from all relational databases.Pattern NAHourly
MSAS:Processing-Total Rows writtenPerfmonCount of rows written during processing.Pattern NAHourly
MSAS:Storage Engine Query- aggregation hits/secPerfmonRate of aggregation hits.Pattern NAHourly
MSAS:Storage Engine Query- Avg time/queryPerfmonAverage time per query, in milliseconds.  Response time based on queries answered since the last counter measurement.Pattern NAHourly

SQL SERVER REPORTING SERVICES

 CounterSourceDescriptionTypeThresholdFrequency
ReportServer:Service- Request RejectedPerfmonTotal number of requests not executed because of insufficient server resources.
This counter represents the number of requests that return a 503 HTTP status code, indicating that the server is too busy.
Alert basedNAOn Occurrence
ReportServer:Service- Memory Pressure StatePerfmonA number from 1-5 indicating the current memory state of the server.
1:No pressure, 2:Low Pressure,3:Medium Pressure,4:High Pressure,5:Exceeded Pressure.
Pattern basedNADaily
ReportServer:Service- Request DisconnectedPerfmonNumber of requests that have been disconnected due to a communication failure.Pattern basedNADaily
ReportServer:Webservice – Requests/SecPerfmonNumber of requests per second.Pattern basedNADaily
ReportServer:Webservice – Reports Executed/SecPerfmonNumber of successful report executions per second.
This counter provides statistics about report volume. Use this counter with Request/Sec to compare report execution to report requests that can be returned from cache.
Pattern basedNADaily
Reports Performance MonitroingReports Server DatabaseMonitor report execution time for each SSRS reportPattern basedNAWeekly

SQL SERVER SERVICE BROKER

CounterSourceDescriptionTypeThresholdFrequency
MSSQL:Broker Activation- Stored Procedure Invoked/SecPerfmonThe number of stored procedures that are being invoked per second.Pattern NAHourly
MSSQL:Broker Activation- Task Aborted/SecPerfmonThe number of activated tasks that are being aborted per second.Pattern NAHourly
MSSQL:Broker Activation- Task Started/SecPerfmonThe number of activated tasks that are being started per second.Pattern NAHourly
MSSQL:Broker Statistics- Activation Errors TotalPerfmonThe number of times an activation stored procedure exited with an error.Pattern NAHourly
MSSQL:Broker Statistics- Corrupted Messages TotalPerfmonThe number of corrupted messages that were received by the instance.Pattern NAHourly
MSSQL:Broker Statistics- SQL RECEIVES/secPerfmonThe number of Transact-SQL RECEIVE statements processed per second.Pattern NAHourly
sys.dm_broker_activated_tasksSQL Server DMVContains a row for each stored procedure activated by Service Broker.Pattern NAHourly
sys.dm_broker_connectionsSQL Server DMVContains a row for each Service Broker network connection.Pattern NAHourly
sys.dm_broker_forwarded_messagesSQL Server DMVContains a row for each Service Broker message that the SQL Server instance is in the process of forwarding.Pattern NAHourly
sys.dm_broker_queue_monitorsSQL Server DMVContains a row for each queue monitor in the instance. A queue monitor manages activation for a queue.Pattern NAHourly

 

Regards

Ritesh Medhe

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

SQL Server Webcast - SQLServerGeeks weekly webcast – 21st February 2012 at 3 p.m. IST
SQL Server Real-time Monitoring using WMI classes: Part -1