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