How to get useful information from sys.dm_os_performance_counters

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.

sys.dm_os_performance_counter

chart_small

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.

distinct_cntr_type_query

Now we have our cntr_type but we need to figure out what these numbers mean.

cntr_type

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.

PERF_AVERAGE_BULK:

537003264: Decimal:

1073874176: Decimal:

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.

query2

Using the query above written by Amit Banerjee twitter | blog

The below image is what we will see in the results. Still not the useful data we are after but we are getting closer.

query2_return

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.

query_per_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.

Cheers!

Reference:
sys.dm_os_performance_counter
http://technet.microsoft.com/en-us/library/ms187743(v=sql.105).aspx

WMI Performance Counter Types (Windows)
http://msdn.microsoft.com/en-us/library/windows/desktop/aa394569(v=vs.85).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s