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
Comments