sys.dm_os_performance_counters Explained

See link for more details: http://rtpsqlguy.wordpress.com/2009/08/11/sys-dm_os_performance_counters-explained/

and another nice one: http://kswain.blogspot.com/2008/04/sysdmosperformancecounters-dynamic.html

The Five Values Of Cntr_Type

1. Cntr_Type = 65792

This is the easiest one, because what you get is the counter’s actual value, the whole value, and nothing but the value. For example, the SQLServer:Buffer Manager / Total pages counter is of this type. It shows the number of (8k) pages in SQL Server’s buffer pool, and on one of my servers shows 332272 representing the 2.5GB of memory allocated to that instance of SQL. Every time you query the DMV, the result is the real-time value for that counter.

2. Cntr_Type = 537003264

Counters of this type are also real-time results, but with an added complexity that they need to be divided by a “base” to obtain the actual value. By themselves, they are useless … kind of like how a car is useless without gas in it … unless you drive an EV but I digress. So what is this base value? It is, literally, the same counter with the word “base” tacked onto the counter name. If you’re sorting the DMV’s results by counter_name, you’ll see the two rows next to each other. You’ll probably also notice that the base value has it’s own counter type: 1073939712. But that’s not important right now. What’s important is that you divide cntr_value from the first row by cntr_value from the second row to get a ratio, or multiply that result by 100.0 to get a percentage (don’t forget that one of the two values needs to be converted to float!)

For example, to get Buffer Cache Hit Ratio, take the value of SQLServer:Buffer Manager / Buffer cache hit ratio and divide by the value of SQLServer:Buffer Manager / Buffer cache hit ratio base. The result is the ratio we all know and love (or hate, if your server’s cache is not running so well). On my system, these two values are:

SQLServer:Buffer Manager / Buffer cache hit ratio = 3154

SQLServer:Buffer Manager / Buffer cache hit ratio base = 3158

SELECT 3154.0 / 3158 results in 0.998733, or 99.87% caching. Not bad.