Version 1

    Description

    DPA stopped using the sp_OA procedures to collect O/S level metrics and started using DMVs. However, the metric O/S CPU Queue Length was not available within a DMV so it was excluded. This document explains how to add it back as a custom metric for SQL Server. Note that this code use the sp_OA procedures which is undocumented and unsupported by Microsoft and SolarWinds.

     

    Metric Definition

    To create the custom metric, click on Options -> Custom Resource Metrics and configure the metric similar to this:

     

    Database Versions: 10.0.0 and above

    Display Name:  O/S CPU Queue Length

    Description:  Using the sp_OA stored procedures, collect the O/S CPU Queue Length from WMI

    Category:  CPU

    Units:  Queue

    Metric Type:  Single Value

    Frequency:  60

    Timeout:  20

     

    SQL Statement:

     

    declare
    @WmiServiceLocator int,
    @WmiService int,
    @CounterObject int,
    @CPUQueLen varchar(21)
    
    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. See: http://www.solarwinds.com/documentation/kbloader.aspx?kb=5914', 16, 1)
     return
    end
    
    exec sp_OAMethod @WmiService, 'Get', @CounterObject output, 'Win32_PerfRawData_PerfOS_System=@'
    exec sp_OAGetProperty @CounterObject, 'ProcessorQueueLength', @CPUQueLen output
    
    if @CPUQueLen is null
    begin
      exec sp_OADestroy @CounterObject
      exec sp_OADestroy @WmiService
      exec sp_OADestroy @WmiServiceLocator
      raiserror('Could not access WMI counter: Win32_PerfRawData_PerfOS_System. See: http://www.solarwinds.com/documentation/kbloader.aspx?kb=5914', 16, 1)
      return
    end
    
    select @CPUQueLen
    exec sp_OADestroy @CounterObject
    exec sp_OADestroy @WmiService
    exec sp_OADestroy @WmiServiceLocator