Minute wise SQL Server CPU consumption calculation.
The query below will calculate the minute wise SQL Server CPU calculation. It will push the data into a Physical table called "Main". There would be a record for each minute handled elimanaton of duplicate records if you run the query multiple times.
--- /* ----2023-06-16 15:04:00.000 SELECT * FROM Main ORDER BY EventTime DESC DELETE FROM Main --- */ DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); MERGE Main AS Targets USING ( SELECT TOP(60) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], left(convert(varchar(16),DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()),120),16)AS [Event Time] FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%%') AS x ) AS y ORDER BY [Event Time] DESC) AS Sources (SQLServerProcessCPUUtilization ,SystemIdleProcess ,OtherProcessCPUUtilization ,EventTime) ON Targets.SQLServerProcessCPUUtilization=Sources.SQLServerProcessCPUUtilization AND Targets.SystemIdleProcess=Sources.SystemIdleProcess AND Targets.OtherProcessCPUUtilization=Sources.OtherProcessCPUUtilization AND Targets.EventTime=Sources.EventTime WHEN NOT MATCHED THEN INSERT(SQLServerProcessCPUUtilization ,SystemIdleProcess ,OtherProcessCPUUtilization , EventTime) VALUES(Sources.SQLServerProcessCPUUtilization,Sources.SystemIdleProcess,Sources.OtherProcessCPUUtilization , Sources.EventTime);
Comments