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

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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?


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: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:

next webcast 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 MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

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    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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