SQL Server CPU usage available in sys.dm_os_ring_buffers DMV starting SQL Server 2019 RC on Linux

This post has been republished via RSS; it originally appeared at: SQL Server articles.

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.

 

image.png

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.