What are WMI classes for SQL server performance counters?

Question

What are WMI classes for SQL server performance counters?

Niranjan Shaiwale

SSChasing Mays

Points: 627
More actions
April 14, 2005 at 4:50 am

#88716

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 classes
for SQL server.
Thanks,
Niranjan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 1

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

Hope this helps!

Niranjan Shaiwale SSChasing Mays Points: 627 More actions · Answer 2

WOW! Excellent information. scriptomatic is a great tool for WMI classes.

That is what I was looking for. That was great help!

Many Thanks.

Niranjan

AngPeng SSC-Addicted Points: 489 More actions · Answer 3

Where is this class :

"Win32_PerfFormattedData_MSSQLSERVER_SQLServerMemoryManager"

I can not find it in my server and not in MSDN.

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 4

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.