The sys.dm_os_performance_counters DMV returns information about SQL Server performance counters that are being collected on the server. The performance counters that you will find in this DMV are the SQL Server performance counters that you would normally find using Windows Performance Monitor.
Lets have a quick look at this DMV to view the columns that are returned from this DMV.
SELECT * FROM sys.dm_os_Performance_counters
The first column returned is the ‘object_name’ column which represents the category that the particular counter belongs to. The second column, ‘counter_name’, is the actual name of the counter. The third column, ‘instance_name’, is usually the database name that the counter is coming from, but sometimes can represent a more another category group name for certain performance counters that are instance scoped as opposed to database scope. The fourth column in the result set is ‘cntr_value’ which is the current value for a specific counter. The ‘cntr_type’ column is used to define the type of counter according to the Windows performance architecture. For many of the performance counters, this cntr_type column is necessary to properly calculate the value of a counter.
This DMV provides useful information about practically every SQL Server performance counter out there. I use it occasionally for very specific counters I am interested in such as Logins/Sec, Page Reads/Sec, or Memory Grants Pending but for most of my performance counter needs, I will still rely on the Windows Performance Monitor tool. I am in no way implying that you should not use this DMV, me personally, I just find the Windows Performance monitor tool a little easier to work with for my needs. Once I become more skilled with how to properly use the ‘cntr_type’ column to properly calculate by counter value, then this DMV will probably be more beneficial to me.
However, this is still a powerful and useful DMV that I felt you should at least know its out there.
For more information about this DMV, please see the below Books Online link:
Follow me on Twitter @PrimeTimeDBA