sys.dm_os_performance_counters Explained

A few weeks ago I had the opportunity to take on a project involving this DMV. I’ve known of its existence since early SQL2005 days, when we were introduced to the “new” DMV capabilities in that release of SQL Server, but have never had the initiative nor the opportunity to use this particular one … until now, that is.

So what is this DMV? For that matter, what is a DMV? No, it’s not the place where you go renew your license and get a bad photo (if you’re like me) that you have to live with for ten years. DMV stands for Dynamic Management View and it’s SQL2005’s way of giving you all kinds of cool information about the state of the server engine and various processes that are tracked internally. There are dozens of these views available for various purposes; I won’t go into the list here but you can link to Books Online for more info or just search the web. There are tons of articles already out there on the topic.

Which brings me to the point of this post. On this one particular DMV, I found nearly no information in either Books Online or on the web in general to explain its use. Seemed like a good opportunity to roll up the sleeves, figure it out on my own, and then write up my findings … so here we are.

My project was to start logging SQL Server performance counters to a database, so that I could build a set of SSRS reports off of the info. Sounds pretty straightforward, right? I thought so too. And, on the surface it is … create a SQL Agent task that periodically saves a snapshot of dm_os_performance_counters to a table. Report on it. Done! Uh, yeah…   until I pulled up a graph of SQLServer:Buffer Manager / Buffer cache hit ratio, expecting to see a trendline somewhere around 99%. Instead I see 3154%. What? Talk about great caching! How about Page reads/sec, another favorite of mine. 162793782?? I know disks are fast, but I know better than to believe that I’m processing 1.2TB of data every second.

Digging deeper into the DMV I noticed that, along with the results I was looking for, comes a column called cntr_type. A quick query returned five different seemingly-random values for this column.  This is when I turned to the web, only to find a World Wide Black Hole of results. After some digging on my own, and running SQL and Perfmon side-by-side, I deciphered these values; below is the explanation. Hopefully someone else out there can benefit.

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.

More practically, here’s a query to return all ratio-typed counter values:

SELECT CntrVal.object_name, CntrVal.counter_name, CntrVal.instance_name,
       CASE WHEN CntrBase.cntr_value = 0
            THEN 0
            ELSE CAST(CntrVal.cntr_value AS FLOAT) / CntrBase.cntr_value
       END AS CounterValueRatio
FROM sys.dm_os_performance_counters CntrVal
  JOIN sys.dm_os_performance_counters CntrBase
    ON CntrVal.object_name = CntrBase.object_name
      AND CntrVal.instance_name = CntrBase.instance_name
      AND (
           RTRIM(CntrVal.counter_name) + N' Base' = CntrBase.counter_name
           OR (
               CntrVal.counter_name = N'Worktables From Cache Ratio'
               AND CntrBase.counter_name = N'Worktables From Cache Base'
              )
          )
WHERE CntrVal.cntr_type = 537003264

A little explanation on the complex join: counter_name is a nchar() data type. Not nvarchar, but nchar. Before appending N’ Base’ to the name, I had to RTRIM the column to remove excess spaces. Second, there is one counter that does not fit the “append ‘Base’ to the name” rule (always an exception!), so we have to account for it in its own special comparison clause. That counter is “Worktables From Cache Ratio” and its base is “Worktables From Cache Base”.

---

Part II – and I didn’t plan on a part II when I first sat down and started this post – alas will have to wait for another time. Believe me when I say that we’re just getting started.

Later!