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