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