Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


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:Buffer Partition
SQLServer:User Settable
SQLServer:Exec Statistics
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:Wait Statistics
SQLServer:Access Methods
SQLServer:Broker/DBM Transport
SQLServer:Broker Statistics
SQLServer:General Statistics
SQLServer:SQL Statistics
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 »