Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What are WMI classes for SQL server performance counters? Expand / Collapse
Author
Message
Posted Thursday, April 14, 2005 4:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 3:39 AM
Points: 41, Visits: 9

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

Post #174674
Posted Friday, April 15, 2005 9:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:04 PM
Points: 60, Visits: 254

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
   Next
Next

Hope this helps!




Post #175043
Posted Saturday, April 16, 2005 1:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 3:39 AM
Points: 41, Visits: 9

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

Post #175175
Posted Monday, April 18, 2005 7:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:26 AM
Points: 43, Visits: 43

Where is this class :

"Win32_PerfFormattedData_MSSQLSERVER_SQLServerMemoryManager"

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




Post #175313
Posted Monday, April 18, 2005 9:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:04 PM
Points: 60, Visits: 254

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.

 




Post #175389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse