sys.dm_os_performance_counters Data Pivoted

At times I often want to know what is happening inside my SQL Servers, there are of course a multitude of ways I could "peek" into the activity that has been happening,

Sometimes I just need to get a quick summary of those facts, maybe just to know if anything unusal has happened I'm not yet aware of, an easy way I can do that is to query the DMV sys.dm_os_performance_counters,

As there are tons of blog posts already out there on using the data from this DMV, I'm not going to focus on that but I wanted to share a simple approach I sometime use with this and some other DMVs and that is to PIVOT the data for "Easy Viewing"

I do like to sometimes to retrun my results in the "Pivoted" fashion for easy viewer and the results from this DMV often lend themselves well to this approach -  What I'm I talking about, well let me show you a few examples to illustrate the point

NEW in SQL Server 2012 are the counters for ”Batch Resp Statistics”

SELECT [counter_name],"CPU Time:Total(ms)","CPU Time:Requests","Elapsed Time:Total(ms)","Elapsed Time:Requests"

FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%Batch Resp Statistics%') os_pc

PIVOT (AVG([cntr_value]) FOR [instance_name]

IN ("CPU Time:Total(ms)","CPU Time:Requests","Elapsed Time:Total(ms)","Elapsed Time:Requests") ) AS Pvt;

Can give you something like the following output


Another is reviewing locks

SELECT [counter_name],"OibTrackTbl","AllocUnit","HoBT","Metadata","Application","RID","Extent","Key","Page","Object","File","Database","_Total"

FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%:Locks%') os_pc

PIVOT (AVG([cntr_value]) FOR [instance_name] IN ("OibTrackTbl","AllocUnit","HoBT","Metadata","Application","RID","Extent","Key","Page","Object","File","Database","_Total")) AS Pvt;


The final one I'll show you is the "Waits" in progress me system not much to see)

SELECT [counter_name],"Average wait time (ms)","Waits in progress","Waits started per second","Cumulative wait time (ms) per second"

FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%Wait Statistics%') os_pc

PIVOT (AVG([cntr_value]) FOR [instance_name]

IN ("Average wait time (ms)","Waits in progress","Waits started per second","Cumulative wait time (ms) per second")) AS Pvt

WHERE "Average wait time (ms)" > 0;

Not much to see in this one as it is my Home system


The examples Queries as you can see are straight forward to compose, I know you can make a few of your own using the same pattern

Your challenge will be figuring out the steps to take to "Solve the performance hurdles" these type of Queries can reveal

Published Sunday, August 19, 2012 10:15 PM by NeilHambly


No Comments