This DMV can be very useful to get information about SQL server. I need to point out a few key points before going too far into how we can use it. While it does return a wealth of information it does not return any counters outside of SQL Server for obvious reasons. The data returned can be a little confusing as well. Without further ado let’s get into it.
See the Note on the column named cntr_value
For now let’s take a look at the cntr_type information returned and see if we can clarify that a bit. Using the below query to get the counter types.
Now we have our cntr_type but we need to figure out what these numbers mean.
If we look on msdn we find information to help understand…hopefully 🙂
PERF_COUNTER_LARGE_RAWCOUNT: 65792: Decimal: This value does not need calculations is the same as PERF_COUNTER_RAWCOUNT (65536) but is 64-bit for larger numbers values
PERF_COUNTER_BULK_COUNT: 27266576: Decimal: Average number of operations completed during each second of the sample interval. This counter type is the same as the PERF_COUNTER_COUNTER type, but it uses larger fields to accommodate larger values.
Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type.
PERF_LARGE_RAW_BASE: 1073939712: Decimal: This counter type collects the last observed value. It is the same as the PERF_RAW_BASE counter type except that it 64-bit and uses larger fields to accommodate larger values.
The below image is what we will see in the results. Still not the useful data we are after but we are getting closer.
Now on the first image there is a Note we need to be aware of.
For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.
Keeping that in mind, if we look at the cntr_type 272696576 definition it is an “each second” value. We can run the query below gather the values run it again in say 1 second and determine our value for one second.
As you can see this DMV has some very useful information but at face value can be a little confusing. Only after digging deeper into the details on the cntr _type and doing additional querying and calculations can we get useful data.
WMI Performance Counter Types (Windows)