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

 Counter Source Description Type Threshold Frequency
SQLServer: Access Methods — Page Splits/sec Perfmon if page splits are causing performance issue.
Increase fill factor or rebuild indexes
Pattern based NA Hourly
SQLServer: Buffer Manager — Buffer Cache Hit Ratio Perfmon To find out if there is enough memory in the server Pattern based NA Hourly
SQLServer: General Statistics — User Connections Perfmon To see how many connections (and users) are using the server Pattern based NA Hourly
SQLServer: Memory Manager — Total Server Memory (KB) Perfmon How much memory SQL server is actually using Pattern based NA Hourly
SQLServer:Errors- Errors/Sec Perfmon Number of errors per second Pattern based NA Hourly
SQLServer:Locks- Number Of deadlocks/Sec Perfmon Number of deadlocks Pattern based NA Hourly
SQLServer:SQL Statistics- SQL Compilations/Sec Perfmon Compiles per second Pattern based <2/sec negligible,
2-20/sec could be investigated,
20-100 poor,
>100 potentially serious
Hourly

SQL SERVER AGENT

Counter Source Description Type Threshold Frequency
SQLAgent:Jobs-Failed Jobs Perfmon Failed Jobs Pattern based NA Daily
SQLAgent:Jobs-Job success rate Perfmon Job success rate Pattern based NA Daily

SQL SERVER INTEGRATION SERVICES

Counter Source Description Type Threshold Frequency
SQL Server:SSIS Service- SSIS package Instances Perfmon The number of package instances currently running. Pattern  NA Hourly 

SQL SERVER ANALYSIS SERVICES

Counter Source Description Type Threshold Frequency
MSAS:Cache-Total Direct Hits Perfmon Total count of direct cache hits.  Queries were answered from existing cache entries. Pattern  NA Hourly
MSAS:Connections-Failures/Sec Perfmon Rate of connection failures. Pattern  NA Hourly
MSAS:Connections-Requests/Sec Perfmon Rate of connection requests.  These are arrivals. Pattern  NA Hourly
MSAS:Connections-Total Failures Perfmon Total failed connection attempts. Pattern  NA Hourly
MSAS:Locks-Current Locks Perfmon Current number of locked objects. Pattern  NA Hourly
MSAS:Locks-Total Deadlocks Dected Perfmon Total number of deadlocks detected. Pattern  NA Hourly
MSAS:Memory-Filestore IO Errors Perfmon Filestore IO Errors total. Pattern  NA Hourly
MSAS:Processing-Total Rows read Perfmon Count of rows read from all relational databases. Pattern  NA Hourly
MSAS:Processing-Total Rows written Perfmon Count of rows written during processing. Pattern  NA Hourly
MSAS:Storage Engine Query- aggregation hits/sec Perfmon Rate of aggregation hits. Pattern  NA Hourly
MSAS:Storage Engine Query- Avg time/query Perfmon Average time per query, in milliseconds.  Response time based on queries answered since the last counter measurement. Pattern  NA Hourly

SQL SERVER REPORTING SERVICES

 Counter Source Description Type Threshold Frequency
ReportServer:Service- Request Rejected Perfmon Total 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 based NA On Occurrence
ReportServer:Service- Memory Pressure State Perfmon A 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 based NA Daily
ReportServer:Service- Request Disconnected Perfmon Number of requests that have been disconnected due to a communication failure. Pattern based NA Daily
ReportServer:Webservice – Requests/Sec Perfmon Number of requests per second. Pattern based NA Daily
ReportServer:Webservice – Reports Executed/Sec Perfmon Number 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 based NA Daily
Reports Performance Monitroing Reports Server Database Monitor report execution time for each SSRS report Pattern based NA Weekly

SQL SERVER SERVICE BROKER

Counter Source Description Type Threshold Frequency
MSSQL:Broker Activation- Stored Procedure Invoked/Sec Perfmon The number of stored procedures that are being invoked per second. Pattern  NA Hourly
MSSQL:Broker Activation- Task Aborted/Sec Perfmon The number of activated tasks that are being aborted per second. Pattern  NA Hourly
MSSQL:Broker Activation- Task Started/Sec Perfmon The number of activated tasks that are being started per second. Pattern  NA Hourly
MSSQL:Broker Statistics- Activation Errors Total Perfmon The number of times an activation stored procedure exited with an error. Pattern  NA Hourly
MSSQL:Broker Statistics- Corrupted Messages Total Perfmon The number of corrupted messages that were received by the instance. Pattern  NA Hourly
MSSQL:Broker Statistics- SQL RECEIVES/sec Perfmon The number of Transact-SQL RECEIVE statements processed per second. Pattern  NA Hourly
sys.dm_broker_activated_tasks SQL Server DMV Contains a row for each stored procedure activated by Service Broker. Pattern  NA Hourly
sys.dm_broker_connections SQL Server DMV Contains a row for each Service Broker network connection. Pattern  NA Hourly
sys.dm_broker_forwarded_messages SQL Server DMV Contains a row for each Service Broker message that the SQL Server instance is in the process of forwarding. Pattern  NA Hourly
sys.dm_broker_queue_monitors SQL Server DMV Contains a row for each queue monitor in the instance. A queue monitor manages activation for a queue. Pattern  NA Hourly

 

Regards

Ritesh Medhe

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

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