SQL Server Monitoring - Important SQL Server Monitroing Counters

Who is online?  89 guests and 0 members
home  »  blogs  »  Ritesh Medhe  »  SQL Server Monitoring - Important SQL Server Monitroing Counters

Training on Microsoft Products & Technologies

SQL Server Monitoring - Important SQL Server Monitroing Counters

change text size: A A A

posted  2/16/2012 12:26:02 PM  by Ritesh Medhe -  Views: [10230] 

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

 

If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

tags : Performance monitoring counters, SQL Server Agent Performance monitoring counters, SQL Server Database Engine Performance monitoring counters, SQL Server monitoring, SQL Server monitoring counters, SQL Server Performance monitoring, SQL Server Service Broker Performance monitoring counters, SSAS Performance monitoring counters, SSIS Performance monitoring counters, SSRS Performance monitoring counters
  Rate This Blog Entry:  register  or  login

Author

riteshmedhe Ritesh Medhe (Member since: 3/31/2011 11:34:25 AM)

View Ritesh Medhe 's profile

Comments (3)

calin
3/2/2012 7:30:56 AM calin said:

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?

by
Alex
3/8/2012 6:15:38 AM Alex said:

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.

by
Robert L Davis
3/11/2012 7:53:32 PM Robert L Davis said:

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.

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Ritesh Medhe's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • SQL Server Day in Gurgaon in May 05’ 2012
    4/30/2012 5:05:54 AM by Admin Admin  - Views: 280  - Comments: 0
    Dear SQL Geek, SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And after successful events in Bangalore, Kolkata & Mumbai, we a...
  • SQL Server 2012 Feature Pack
    4/23/2012 9:24:41 AM by Quick Tip  - Views: 241  - Comments: 0
    Hi SQL Geeks, The Microsoft® SQL Server® 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012. You can download the ...
  • SQL Server QUOTENAME function
    4/23/2012 9:09:51 AM by Amit Bansal  - Views: 1160  - Comments: 4
    Hi Friends, Here is an interesting function: QUOTENAME() which returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. (from Books Online) ...
  • SQL Server 2008 R2 Express Database size limit
    4/12/2012 8:20:10 AM by Ravi Kumar  - Views: 926  - Comments: 0
    Hi All, It is a great news to all those who use SQL Server Express editions. Microsoft has increased the database size limit of SQL Server 2008 R2 Express edition from 4GB to 10GB. Previous versions o...
  • SQL Server – What is Scalar Function?
    4/11/2012 7:04:48 AM by Piyush Bajaj  - Views: 3239  - Comments: 2
    Hello Friends, It’s been a while that I have posted any blog. So here it is- Well with the arrival of SQL Server 2000, Microsoft has introduced the concept of User Defined Function (UDF). So the...
  • SQL Server 2012 Released to Manufacturing
    4/9/2012 11:41:31 AM by SQLnews SQLnews  - Views: 325  - Comments: 0
    Exciting news! SQL Server 2012 has released to manufacturing. Customers and partners can download an evaluation of the product today and can expect general availability to begin on April 1. Microsoft ...