MORE ABOUT ME

WMI Code Creator

06 Jan WMI Code Creator

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.

2 Pingbacks/Trackbacks

  • http://sqlblog.com/blogs/aaron_bertrand/ Aaron Bertrand

    I see those counters just fine. Is it possible they’re missing in an isolated case (see http://blogs.msdn.com/b/john_daskalakis/archive/2009/05/07/9593110.aspx)? Is it possible you’re running 32-bit SQL on a 64-bit OS?

    • Thomas LaRock

      no, you’re right, i missed them the first time around. sorry about that, but the idea is still valid, you can use the code generator as a shortcut to get the names for other counters.

    • Thomas LaRock

      Aaron,

      if i have some time later today i will see if i can update the example to use a different counter, like avg disk read/sec. the code generator just makes it easy for me to navigate WMI code.

  • http://www.ryanjadams.com Ryan Adams

    In my opinion, WMI is one of MS’s hidden gems. The code creators are great but I also use the WMI Explorer to find the data class I am looking for.

    • Thomas LaRock

      Ryan,

      I forgot to mention the WMI explorer, thanks! I do wish there was a way to get to WMI without having to resort to sp_OA in TSQL.

  • http://sqlblog.com/blogs/aaron_bertrand/ Aaron Bertrand

    But Tom, how often are you really collecting these (let’s call them “obscure”) counters from within T-SQL? Seems to me there are far better ways to collect perf counters than this … especially when you layer in the combined overhead of WMI, sp_OA, and the engine to get to something that is much more efficient to access through LogMan or (dare I suggest it) PowerShell, providing also a consistent way to do it whether you are working on a database server or not (and also allowing you to perform the collection remotely). There are some WMI things that can be useful from within T-SQL but I’m not sure perf counters are the best example, IMHO.

    • Thomas LaRock

      Yeah, I should probably build some CLR to get this done instead…

      How often? That depends on the solution being deployed. If I am connected to a database server and don’t have the ability to fire up LogMan then using TSQL is a nice option. Is it the best option? Not currently, but it is an option.

  • http://www.youdidwhatwithtsql.com Rhys

    Powershell has got to be the way to go on this IMHO. I wrote a post on this a few weeks ago http://www.youdidwhatwithtsql.com/performance-benchmarking-with-powershell/909

    • Thomas LaRock

      Rhys,

      Thanks for the link. The problem is that I need to get at O/S counters from my database connection. So, if I am connected to the db already, then the natural inclination is to use TSQL. WHile POSH can get the job done, I can’t call it from TSQL, can I? (and boy, wouldn’t it be nice if I could!)

  • http://www.youdidwhatwithtsql.com Rhys

    Yeah, I get your thinking (I just love Powerhsell to much). As you mention, using CLR to create a quick function, to retrieve these counters, shouldn’t take too long.

  • http://sqlblog.com/blogs/aaron_bertrand/ Aaron Bertrand

    Just FYI the message for the RAISERROR you added toward the end of the code sample is missing a closing single quote.

    • Thomas LaRock

      Thanks Aaron, should be fixed now.

  • Pingback: Aaron Bertrand : The fallacy of preventing plagiarism()

  • Pingback: March Madness – SQL Server System Tables – sysperfinfo | SQLRockstar()