SQL Server Monitoring - Important SQL Server Monitroing Counters

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

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: [16418] 

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)
Ritesh Medhe is a Microsoft Certified DBA & a BI professional with over 8 years of experience. Expertise: Performance optimization & SQL Server Administration

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

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Ritesh Medhe's latest blog posts

  • SQL Server - How big is Big Data?
    8/16/2012 7:37:20 AM by Ritesh Medhe  - Views: 1172  - Comments: 1
    We have heard people talking about big data especially DWH folks. Do we really know the definition of BIG Data? Majority of us would attribute big data solely to the size of data. Well to know the rea...
  • SQL Server: Expose OLAP Cube over HTTP port 80
    2/28/2012 10:08:58 AM by Ritesh Medhe  - Views: 11716  - Comments: 3
    This white paper will navigate you through the process of exposing SSAS cubes over port 80 Prequisites : SSAS server must have IIS installed (if both the servers are different you might need to setup ...
  • SQL Server Monitoring - Automating SQL Server Health Checks (SQL Server 2005 & Above)
    2/20/2012 12:01:50 PM by Ritesh Medhe  - Views: 88585  - Comments: 3
    It's a known fact that an ideal Production DBA is like a parent to 100's of children. By children I mean database servers, and yes, they are like babies to DBAs. No matter how unpleasant your child is...
  • SQL Server Monitoring - Important SQL Server Monitroing Counters
    2/16/2012 12:26:02 PM by Ritesh Medhe  - Views: 16419  - Comments: 3
    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 perspectiv...
  • SQL Server - Identify Unused Tables & Indexes
    1/9/2012 11:35:17 AM by Ritesh Medhe  - Views: 15837  - Comments: 8
    It often happens that DBA’s/Developers end up creating database objects(mostly tables) during development phase for various reasons like intermediate testing or backup . Such objects often end u...
  • SQL Server - Get TOP 5 Tables Consuming Maximum Space
    1/2/2012 7:39:51 AM by Ritesh Medhe  - Views: 47923  - Comments: 6
    I always say that database sizing is one of the prime tasks and concern for any DBA. As a DBA you should ensure that there is no single record claiming space in the databasefor no reason. DBA's should...
  • SQL Server - Identify Long Running Queries
    12/27/2011 12:35:02 PM by Ritesh Medhe  - Views: 64998  - Comments: 6
    It is always a big challenge to maintain application’s performance optimum. Increasing data on regular basis is often the cause for performance degrades. In most of the cases end users report th...
  • SQL Server: SQL-Getting rid of unused space in table
    12/24/2011 8:26:45 AM by Ritesh Medhe  - Views: 138259  - Comments: 6
    Scenario: Database sizing is one of the most critical tasks for any DBA. One of the DBA’s tasks is to also ensure optimum performance. Database/table size is one of the key factors impacting the...
Blogs RSS Feed

Latest community blog posts

  • Creating Dynamic SQL/MDX using Script Task in SSIS
    6/14/2013 1:19:40 PM by Raunak Jhawar  - Views: 153  - Comments: 0
    Of late I have been working on creating a lot of SSIS packages and a new trick that I have come up with is to use a Script Task to create a dynamic SQL and/or MDX for me using standard SSIS variables ...
  • Database Restore with Snapshot - Backup Chain Breaker
    6/11/2013 2:49:29 PM by prince rastogi  - Views: 234  - Comments: 0
    Hi Friends, Today I will show you practical for one of the reason of Transaction Log backup chain breaker i.e. restoring database with database snapshot. Suppose I am having one database named as SQLT...
  • SQL Server blogs from Piyush Bajaj for the month of May
    6/1/2013 5:16:32 AM by Admin Admin  - Views: 289  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Piyush Bajaj for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at ht...
  • SQL Server blogs from Ahmad Osama for the month of May
    6/1/2013 5:15:02 AM by Admin Admin  - Views: 327  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at htt...
  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 594  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 1186  - Comments: 2
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 2945  - Comments: 1
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 1561  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 2968  - Comments: 6
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 1638  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 552  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 2232  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...