Systems Engineering and RDBMS

Archive for May 1st, 2007

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.

Posted in SQL Server | 2 Comments »