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
At least for SQL Server – database engine, a bit misleading and incomplete. Better check the free list from PAL, perhaps? As a great starting point?
Completely agree with calin, the list is misleading and incomplete. For example:
Buffer Cache Hit Ratio – Doesn’t show you if there is enough memory at all.
Page Splits/sec – you cannot get too much info from this one, because it count creating new pages as splits too.
Agree with the others. Take for example the counter SQLAgent:Jobs-Failed Jobs. It lists that it is pattern based. When would any value greater than 0 be acceptable for this counter? And a much better counter for determining if you have enough memory is Page Life Expectancy. This tells you how frequently pages are getting aged out of cache. Though I really prefer to poll the DMVs to get the pattern of memory grants pending.