These days I find myself digging through waits and queues to a greater degree than in my previous role. And as much as I love the sys.dm_os_performance_counters DMV it does not contain each and every performance counter that I might find useful.

One way to get to those counters is to use WMI. The downside to this is that you have to use WMI. For anyone that has ever tried to navigate the objects and methods you know this can be a tedious task at best.

I recently came across a great tool called the WMI Code Creator, you can download a copy of the app for yourself here:

https://www.microsoft.com/downloads/en/details.aspx?FamilyID=2cc30a64-ea15-4661-8da4-55bbc145c30e&displaylang=en

This application is a huge time saver for me whenever I need it. For example, the sys.dm_os_performance_counters DMV does not contain the Avg Disk Sec/Read counter located in the MSSQL$BACON:Databases object in perfmon. And I have no idea what the WMI code might look like, but with the code generator I am only a few clicks away from this snippet:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
 "SELECT * FROM Win32_PerfRawData_PerfDisk_PhysicalDisk",,48)
For Each objItem in colItems
 Wscript.Echo "-----------------------------------"
 Wscript.Echo "Win32_PerfRawData_PerfDisk_PhysicalDisk instance"
 Wscript.Echo "-----------------------------------"
 Wscript.Echo "AvgDiskBytesPerRead: " & objItem.AvgDiskBytesPerRead
Next

With a little bit more effort I can then arrive at the following T-SQL code:

DECLARE @WmiServiceLocator INT ,
 @WmiService INT ,
 @CounterObject INT ,
 @AvgDiskReadPerSec BIGINT,
 @AvgDiskReadPerSec_Base BIGINT

EXEC sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator OUTPUT, 5
EXEC sp_OAMethod @WmiServiceLocator, 'ConnectServer', @WmiService OUTPUT, '.', 'root\cimv2'

IF ISNULL(@WmiService, -1) <= 0
 BEGIN
 EXEC sp_OADestroy @WmiServiceLocator          
 RAISERROR('Could not access WMI service.', 16, 1)
 RETURN
 END

EXEC sp_OAMethod @WmiService, 'Get', @CounterObject OUTPUT, 'Win32_PerfRawData_PerfDisk_PhysicalDisk="_Total"'
EXEC sp_OAGetProperty @CounterObject, 'AvgDiskSecPerRead', @AvgDiskReadPerSec OUTPUT
EXEC sp_OAGetProperty @CounterObject, 'AvgDiskSecPerRead_Base', @AvgDiskReadPerSec_Base OUTPUT
 
EXEC sp_OADestroy @CounterObject
EXEC sp_OADestroy @WmiService
EXEC sp_OADestroy @WmiServiceLocator   

IF @AvgDiskReadPerSec IS NULL OR @AvgDiskReadPerSec_Base IS NULL
BEGIN
RAISERROR('Could not access WMI counter: Win32_PerfRawData_PerfDisk_PhysicalDisk.Name="_Total"', 16, 1)
RETURN
END

SELECT @AvgDiskReadPerSec, @AvgDiskReadPerSec_Base

Yeah, I know that using sp_OA methods are not always the most ideal things to use, like I said before this is just one way to get the job done and happen to be the one I am most familiar with. I am hoping that in Denali we will get access to a lot more counters in the sys.dm_os_performance_counters DMV and I won’t have to use these procedures as often.