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.
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?
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.
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.
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.
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.
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.
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.
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
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!)
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.
Just FYI the message for the RAISERROR you added toward the end of the code sample is missing a closing single quote.
Thanks Aaron, should be fixed now.
I like the WMI Code Creator and have been using it for years. However, I’ve recently found that the open-source WMI Delphi Code Creator is far superior.
It’s open-source too: http://code.google.com/p/wmi-delphi-code-creator/
Don’t let the name fool you, it can generate code for querying WMI classes, executing WMI methods, and receiving WMI events in Microsoft C++, Borland C++, C#, Delphi, Free Pascal. Check it out, you’ll be glad you did.