Systems Engineering and RDBMS

Tracking SQL Server perfmon counters through T-SQL

Posted by decipherinfosys on May 1, 2007

In SQL Server 2005, one of the new dynamic management views is : sys.dm_os_performance_counters.  This DMV can be used to query the view directly and monitor the SQL Server related performance counters.  Depending upon the services and applications installed, the number of counters will vary – these counters will range from memory counters to application specific counters…here are the ones that are on my local instance:

SELECT distinct object_name FROM sys.dm_os_performance_counters

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

The columns returned (besides the object_name) are the counter_name, instance_name, cntr_value (value for that counter) and cntr_type (specific counter types).  Also, this is the DMV that is a replacement for the sysperfinfo view.  As far as permissions go, access to this view requires “VIEW SERVER STATE” permissions on the server.

2 Responses to “Tracking SQL Server perfmon counters through T-SQL”

  1. […] on February 16, 2008 In some of our previous blogs, we have talked about performance monitor (and tracking it within SQL Server) as well as the ability to co-relate the perfmon data with the SQL Server Profiler data in SQL […]

  2. […] on June 12, 2009 We had briefly covered this DMV in one of our blog posts before – here.  This DMV can be used for tracking SQL Server related perfmon counters through plain simple T-SQL […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: