How to retrieve Linked Server CPU utilisation details?

On 29 May, 2015

MS SQL DBA

By : Charith Silva

No Comments

If you are suffering from poorly performing linked server queries, you may wants to investigate the Linked Server CPU utilisation statistics. Following T-SQL script retrieves information such as SQL Process utilisation, System Idle time and Other Process utilization for a preconfigured MS SQL Linked server.

DECLARE @LinkedServerName varchar(128)
SET @LinkedServerName='Linked Server Name' -- Set your linked server name here

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = 'SELECT record_time,SQLProcessUtilization,SystemIdle,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization'
SELECT @SQL = @SQL +' FROM (SELECT record.value(''(./Record/@id)[1]'', ''bigint'') AS record_id, record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''bigint'') AS SystemIdle,'
SELECT @SQL = @SQL +' record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'', ''bigint'') AS SQLProcessUtilization,record_time'
SELECT @SQL = @SQL +' FROM ( select dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time, cast(r.record as xml) record '
SELECT @SQL = @SQL +' from ['+@LinkedServerName+'].master.sys.dm_os_ring_buffers r cross join ['+@LinkedServerName+'].master.sys.dm_os_sys_info sys where ring_buffer_type=''RING_BUFFER_SCHEDULER_MONITOR'' AND record LIKE ''%<SystemHealth>%'') AS x ) AS y ORDER By record_time DESC'

EXEC(@SQL)

DECLARE @LinkedServerName varchar(128)

SET @LinkedServerName='Linked Server Name' -- Set your linked server name here

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = 'SELECT record_time,SQLProcessUtilization,SystemIdle,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization'

SELECT @SQL = @SQL +' FROM (SELECT record.value(''(./Record/@id)[1]'', ''bigint'') AS record_id, record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''bigint'') AS SystemIdle,'

SELECT @SQL = @SQL +' record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'', ''bigint'') AS SQLProcessUtilization,record_time'

SELECT @SQL = @SQL +' FROM ( select dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time, cast(r.record as xml) record '

SELECT @SQL = @SQL +' from ['+@LinkedServerName+'].master.sys.dm_os_ring_buffers r cross join ['+@LinkedServerName+'].master.sys.dm_os_sys_info sys where ring_buffer_type=''RING_BUFFER_SCHEDULER_MONITOR'' AND record LIKE ''%<SystemHealth>%'') AS x ) AS y ORDER By record_time DESC'

EXEC(@SQL)

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva