Home
%3CLINGO-SUB%20id%3D%22lingo-sub-825361%22%20slang%3D%22en-US%22%3ESQL%20Server%20CPU%20usage%20available%20in%20sys.dm_os_ring_buffers%20DMV%20starting%20SQL%20Server%202019%20RC%20on%20Linux%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ESys.dm_os_ring_buffers%20DMV%20has%20been%20a%20key%20DMV%20used%20for%20monitoring%20SQL%20Server%20by%20built-in%20tools%20as%20well%20as%20third%20party%20monitoring%20utilities.%20When%20SQL%20Server%202017%20was%20released%20on%20Linux%2C%20unfortunately%20this%20DMV%20did%20not%20return%20correct%20CPU%20usage%20information%20by%20SQL%20Server%20process.%20SQL%20Server%20team%20is%20glad%20to%20announce%20that%20the%20starting%20with%20SQL%20Server%202019%20release%20candidate%2C%20the%20sys.dm_os_ring_buffers%20DMV%20returns%20SQL%20CPU%20utilization%20correctly.%20This%20improvement%20should%20benefit%20the%20SQL%20Server%20monitoring%20ecosystem%20on%20Linux%20by%20providing%20a%20way%20to%20monitor%20SQL%20Server%20CPU%20usage%20and%20enable%20decision%20making%20to%20engage%20corrective%20action%20if%20required.%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESample%20Query%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eselect%20top%2010%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20id%2C%20SQLServerCPUUtilization%2C%20100%20-%20SystemIdle%20-%20SQLServerCPUUtilization%20as%20NonSQLCPUUtilization%3C%2FP%3E%0A%3CP%3E--%20SystemIdle%20on%20Linux%20will%20be%200%3C%2FP%3E%0A%3CP%3Efrom%20(%3C%2FP%3E%0A%3CP%3Eselect%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20record.value('(.%2FRecord%2F%40id)%5B1%5D'%2C%20'int')%20as%20id%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20record.value('(.%2FRecord%2FSchedulerMonitorEvent%2FSystemHealth%2FSystemIdle)%5B1%5D'%2C%20'int')%20as%20SystemIdle%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20record.value('(.%2FRecord%2FSchedulerMonitorEvent%2FSystemHealth%2FProcessUtilization)%5B1%5D'%2C%20'int')%20as%20SQLServerCPUUtilization%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20timestamp%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20from%20(%3C%2FP%3E%0A%3CP%3Eselect%20timestamp%2C%20convert(xml%2C%20record)%20as%20record%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20from%20sys.dm_os_ring_buffers%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20where%20ring_buffer_type%20%3D%20N'RING_BUFFER_SCHEDULER_MONITOR'%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20and%20record%20like%20'%25%3CSYSTEMHEALTH%3E%25')%20as%20RingBufferInfo%3C%2FSYSTEMHEALTH%3E%3C%2FP%3E%0A%3CP%3E)%20AS%20TabularInfo%3C%2FP%3E%0A%3CP%3Eorder%20by%20id%20desc%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESample%20Results%3A%3C%2FP%3E%0A%3CP%3Eid%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SQLServerCPUUtilization%20NonSQLCPUUtilization%3C%2FP%3E%0A%3CP%3E-----------%20-----------------------%20--------------------%3C%2FP%3E%0A%3CP%3E112%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2086%3C%2FP%3E%0A%3CP%3E111%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2013%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2087%3C%2FP%3E%0A%3CP%3E110%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2014%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2086%3C%2FP%3E%0A%3CP%3E109%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2015%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2085%3C%2FP%3E%0A%3CP%3E108%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2015%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2085%3C%2FP%3E%0A%3CP%3E107%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2015%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2085%3C%2FP%3E%0A%3CP%3E106%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2097%3C%2FP%3E%0A%3CP%3E105%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%209%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2091%3C%2FP%3E%0A%3CP%3E104%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B97%3C%2FP%3E%0A%3CP%3E103%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20100%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(10%20rows%20affected)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECompletion%20time%3A%202019-08-27T22%3A35%3A52.4202651%2B05%3A30%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20same%20information%20in%20graphical%20manner%20using%20Azure%20Data%20Studio.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20602px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128792iCE30421949113A1B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-825361%22%20slang%3D%22en-US%22%3E%3CP%3EStarting%20SQL%20Server%202019%20RC%2C%20sys.dm_os_ring_buffers%20returns%20correct%20CPU%20utilization%20for%20SQL%20Server%20on%20Linux%2C%20enabling%20monitoring%20of%20SQL%20server%20on%20Linux%20deployments.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-825361%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerTiger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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