System Monitor (Perfmon) Counters for SQL Server 2005

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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.

 

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

• With the sys.dm_os_performance_counters DMV you can start to query the Performance Monitor values directly from SQL Server to troubleshoot the instance's performance issues.  So keep these in mind as a future resource.
• The sys.dm_os_performance_counters is a replacement to dbo.sysperfinfo from SQL Server 2000 that is available for backward compatibility. 
• Check out these related MSSQLTips:
  • SQL Server Performance Statistics Using a Server Side Trace
  • SQL Server 2000 to 2005 Crosswalk = Waitstats performance metrics
  • SQL Server statements currently running with fn_get_sql

Related Tips: More | Become a paid author

Last Update: 8/14/2006

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool. SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...