Closed PiJoCoder closed 1 year ago
Something like a starting point:
SET ANSI_NULLS OFF -- this is to allow WHERE value = NULL type of syntax in case @inst_index is NULL
IF ((OBJECT_ID ('dbo.tbl_ServerProperties') IS NOT NULL) AND (OBJECT_ID ('dbo.CounterData') IS NOT NULL) )
BEGIN
DECLARE @process_id INT = 0, @cpu_count int, @inst_name VARCHAR (64), @inst_index INT
SELECT @process_id = PropertyValue
FROM tbl_ServerProperties sp
WHERE sp.PropertyName = 'ProcessID'
SELECT @cpu_count = CASE WHEN PropertyValue = 0 THEN 1 ELSE PropertyValue END
FROM tbl_ServerProperties sp
WHERE sp.PropertyName = 'cpu_count'
--get processID of SQL assumes that the instance was not restarted during data collection and preserved its PID
SELECT TOP 1 @inst_name = InstanceName, @inst_index = InstanceIndex
FROM CounterData ctr JOIN CounterDetails cdet
ON ctr.CounterID = cdet.CounterID
WHERE cdet.ObjectName = 'Process'
AND cdet.CounterName LIKE 'ID Process'
AND cdet.InstanceName like 'sqlservr%'
AND ctr.CounterValue = @process_id
--combine SQL and OS counter data into a data set by using a join
SELECT sql_cpu.CounterDateTime AS EventTime,
sql_cpu.RecordIndex AS record_id_sql,
os_cpu.system_idle_cpu,
CASE WHEN sql_cpu.sql_cpu_utilization > os_cpu.total_cpu_utilization THEN os_cpu.total_cpu_utilization
ELSE sql_cpu.sql_cpu_utilization END AS sql_cpu_utilization,
total_cpu_utilization - (CASE WHEN sql_cpu.sql_cpu_utilization > os_cpu.total_cpu_utilization THEN os_cpu.total_cpu_utilization
ELSE sql_cpu.sql_cpu_utilization END ) AS nonsql_cpu_utilization
FROM
(
-- get SQL CPU for the imported instance
SELECT ctr.CounterDateTime, ctr.RecordIndex, CONVERT(INT, (floor(ctr.CounterValue )/ (100 * @cpu_count)) * 100) as sql_cpu_utilization, InstanceName, InstanceIndex
FROM CounterData ctr JOIN
CounterDetails det
ON ctr.CounterID = det.CounterID
WHERE det.ObjectName = 'Process'
AND det.CounterName LIKE '[%] Processor Time'
AND det.InstanceName = @inst_name
AND det.InstanceIndex = @inst_index ) as sql_cpu
INNER JOIN
(SELECT ctr.CounterDateTime, ctr.RecordIndex, floor(ctr.CounterValue) as total_cpu_utilization, 100 - floor(ctr.CounterValue) as system_idle_cpu
FROM CounterData ctr JOIN
CounterDetails det
ON ctr.CounterID = det.CounterID
WHERE det.ObjectName = 'Processor Information'
AND det.CounterName LIKE '[%] Processor Time'
AND InstanceName = '_Total') as os_cpu
ON
sql_cpu.RecordIndex = os_cpu.RecordIndex
END
Ringbuffer DMV is not supported any more and sometimes produces incorrect values for CPU utilization. So switch to another source of information - perfmon counters