Skip to content

sys.dm_os_performance_counters Explained

August 11, 2009

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!

About these ads

From → PerfCounter DMV

4 Comments
  1. Hi,

    I just hit your post when I was looking for te soluton why I only got ne column when using te below query; (from MSDN site)
    “Select count(*) from sys.dm_os_performance_counters”

    I’m a novice in SQL, only starting to work with t the last 4 weeks.

    My goal is to find an efficient way to report the servers performance.
    Your blog realy hits the spot.

    I realy hope you are going to keep posting and I’m looking forward seeing POST II (got to lov the “To be continued” in TV shows, grrr :) )

    Thanks again for this detailed post

    • rrabin permalink

      Hi Nico,

      Thanks so much! I’m glad you found it helpful already, and I’ll try to get part two posted in the next few days. I noticed that I also need to fix the SQL query in my part one post so it shows up properly.

      Regards
      SQL guy

  2. I’ve been doing the same digging and come to the same conclusions, but I want something that will work on older versions of SQL Server as well. The problem is that master..sysperfinfo in SQL 2000 uses cntr_type 65536 instead of 65792.

    Rather than hard code these values it would be useful to understand how they are decided upon in the first place. I guess a bitmap, as the difference between the two is exactly 256.

    The same applies to 537003264. In SQL 2000 it is 537003008.

    • rrabin permalink

      We manage only a couple of SQL2000 servers in our company, so I’ve been dealing mainly with 2005 for a while now. You could write one set of queries to work on both versions of SQL by using a CASE statement for the counter type, for example

      SELECT * FROM master..sysperfinfo WHERE cntr_type = CASE CAST(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(2)) AS INT) WHEN 8 /* SQL2000 */ THEN 65536 ELSE 65792 END

      A tougher obstacle is that master..sysperfinfo is being deprecated (although it seems to work in both 2005 and 2008) so what you really want is to select from either master..sysperfinfo if SQL2000, or master.sys.dm_os_performance_counters if SQL2005/08. Might have to resort to dynamic SQL for that one.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 85 other followers

%d bloggers like this: