Hi,I want to use WMI to retrieve SQL server performance counters for my Performance Monitor.Can some body tell me what namespace and what classess to use for SQL server performance counters?I could access system info by using other win32 classess at \root\cimv2. I could also get SQL server performance counters by using PerformanceCounter interface in code, but I want to know about WMI classesfor SQL server.
I'm still trying to learn this stuff myself. I couldn't find them in the WMI Object Browser, but using the "Scriptomatic" tool from Microsoft's scripting center I was able to browse through the root\CIMV2 namespace and come up with what looks like the right stuff.
Here's a portion of the script that Scriptomatic generated, which shows what kinds of things are available from the memory manager class. There are lots of other classes with similar names.
For Each strComputer In arrComputers WScript.Echo WScript.Echo "==========================================" WScript.Echo "Computer: " & strComputer WScript.Echo "=========================================="
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerMemoryManager", "WQL", _ wbemFlagReturnImmediately + wbemFlagForwardOnly)
For Each objItem In colItems WScript.Echo "Caption: " & objItem.Caption WScript.Echo "ConnectionMemoryKB: " & objItem.ConnectionMemoryKB WScript.Echo "Description: " & objItem.Description WScript.Echo "Frequency_Object: " & objItem.Frequency_Object WScript.Echo "Frequency_PerfTime: " & objItem.Frequency_PerfTime WScript.Echo "Frequency_Sys100NS: " & objItem.Frequency_Sys100NS WScript.Echo "GrantedWorkspaceMemoryKB: " & objItem.GrantedWorkspaceMemoryKB WScript.Echo "LockBlocks: " & objItem.LockBlocks WScript.Echo "LockBlocksAllocated: " & objItem.LockBlocksAllocated WScript.Echo "LockMemoryKB: " & objItem.LockMemoryKB WScript.Echo "LockOwnerBlocks: " & objItem.LockOwnerBlocks WScript.Echo "LockOwnerBlocksAllocated: " & objItem.LockOwnerBlocksAllocated WScript.Echo "MaximumWorkspaceMemoryKB: " & objItem.MaximumWorkspaceMemoryKB WScript.Echo "MemoryGrantsOutstanding: " & objItem.MemoryGrantsOutstanding WScript.Echo "MemoryGrantsPending: " & objItem.MemoryGrantsPending WScript.Echo "Name: " & objItem.Name WScript.Echo "OptimizerMemoryKB: " & objItem.OptimizerMemoryKB WScript.Echo "SQLCacheMemoryKB: " & objItem.SQLCacheMemoryKB WScript.Echo "TargetServerMemoryKB: " & objItem.TargetServerMemoryKB WScript.Echo "Timestamp_Object: " & objItem.Timestamp_Object WScript.Echo "Timestamp_PerfTime: " & objItem.Timestamp_PerfTime WScript.Echo "Timestamp_Sys100NS: " & objItem.Timestamp_Sys100NS WScript.Echo "TotalServerMemoryKB: " & objItem.TotalServerMemoryKB WScript.Echo NextNext
Hope this helps!
WOW! Excellent information. scriptomatic is a great tool for WMI classes.That is what I was looking for. That was great help!
Where is this class :
I can not find it in my server and not in MSDN.
To see what I saw in Scriptomatic, the counters have to be installed on the machine where you're running Scriptomatic. I think the counter classes are automatically installed with SQL Server.