Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL Server by built-in tools as well as third party monitoring utilities. When SQL Server 2017 was released on Linux, unfortunately this DMV did not return correct CPU usage information by SQL Server process. SQL Server team is glad to announce that the starting with SQL Server 2019 release candidate, the sys.dm_os_ring_buffers DMV returns SQL CPU utilization correctly. This improvement should benefit the SQL Server monitoring ecosystem on Linux by providing a way to monitor SQL Server CPU usage and enable decision making to engage corrective action if required.
Sample Query:
select top 10
id, SQLServerCPUUtilization, 100 - SystemIdle - SQLServerCPUUtilization as NonSQLCPUUtilization
-- SystemIdle on Linux will be 0
from (
select
record.value('(./Record/@id)[1]', 'int') as id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLServerCPUUtilization,
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 '%<SystemHealth>%') as RingBufferInfo
) AS TabularInfo
order by id desc
Sample Results:
id SQLServerCPUUtilization NonSQLCPUUtilization
----------- ----------------------- --------------------
112 14 86
111 13 87
110 14 86
109 15 85
108 15 85
107 15 85
106 3 97
105 9 91
104 3 97
103 0 100
(10 rows affected)
Completion time: 2019-08-27T22:35:52.4202651+05:30
And same information in graphical manner using Azure Data Studio.
Microsoft
