Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

System Monitor (Perfmon) Counters for SQL Server 2005


By:   |   Read Comments (1)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Capturing performance monitor counters is of great value to understand how SQL Server is behaving at a macro level, that being how overall resources are being used within the engine.  Without this data it is difficult to determine where the performance issues are occurring.  Capturing the metrics has been traditionally from Performance Monitor either on an ad-hoc basis or setting up a log to capture the values on a predefined basis. 

If you dug a little further in SQL Server 2000's system tables you probably found the dbo.sysperfinfo table in the master database.  Unfortunately, this table has been converted to a view with SQL Server 2005 and dbo.sysperfinfo is only available for backward compatibility.  Since this is the case, how can I capture the Performance Monitor values on an as needed basis with SQL Server 2005?

Solution

In SQL Server 2005, one of the new dynamic management objects is sys.dm_os_performance_counters.  This DMV makes it possible to query a view directly to capture the SQL Server counters related to the instance.  This offers DBAs a great deal of flexibility to capture the metrics in real time as an issue is occurring.  It is as simple as a SELECT statement to determine the SQL Server performance metrics.  What could be easier?

What is the result set?

A simple SELECT statement against the sys.dm_os_performance_counters view will result in a 5 column result set and 500+ counters.  On my particular instance close to a 1000 counters are being captured and you can expect more with large numbers of databases and by installing all of the SQL Server application components.

 

Column Explanation
object_name Counter category i.e. MSSQL + $ + InstanceName: + Databases (if you have an instance)

NOTE - Depending on the applications and services installed, 20+ categories will be captured for the SQL Server instance.
 

counter_name Counter name relative to the category, which may overlap between various object_name values.
 
instance_name Instance of the counter which is either a database value or if it is a NULL value than the instance name is related to the overall SQL Server.
 
cntr_value The captured or calculated value for the counter.
 
cntr_type Counter type defined by Performance Monitor.

What are the counters?

Depending on the services and applications installed the number of counters will vary, but expect to be able to query at least 500 counters.  These counters range from memory usage to SQL Server application specific counters to include:

  • MSSQL:CLR
  • MSSQL:Access Methods
  • MSSQL:User Settable
  • MSSQL:Buffer Manager
  • MSSQL:Broker Statistics
  • MSSQL:SQL Errors
  • MSSQL:Latches
  • MSSQL:Buffer Partition
  • MSSQL:SQL Statistics
  • MSSQL:Locks
  • MSSQL:Buffer Node
  • MSSQL:Plan Cache
  • MSSQL:Cursor Manager by Type
  • MSSQL:Memory Manager
  • MSSQL:General Statistics
  • MSSQL:Databases
  • MSSQL:Catalog Metadata
  • MSSQL:Broker Activation
  • MSSQL:Broker/DBM Transport
  • MSSQL:Transactions
  • MSSQL:Cursor Manager Total
  • MSSQL:Exec Statistics
  • MSSQL:Wait Statistics

What are the limitations?

Unfortunately, the sys.dm_os_performance_counters is limited to SQL Server counters, so if you want system, physical disk, network interface card, etc. you need to run performance monitor to capture these counters.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, December 24, 2012 - 7:44:33 AM - Prabhakaran Back To Top

How to make Cloud computing with SQL SERVER ,requirement ,needs,h/w &s/w ..etc

 


Learn more about SQL Server tools