Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save jeffjohnson9046/7bd409ace302dd64d66b84dd76c352a8 to your computer and use it in GitHub Desktop.
Save jeffjohnson9046/7bd409ace302dd64d66b84dd76c352a8 to your computer and use it in GitHub Desktop.
-- From this article: https://logicalread.com/troubleshoot-high-cpu-sql-server-pd01/#.WW_hjtPytuY
-- Show CPU Utilization by SQL Server, system idle time and other processes
DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 15 record_id
,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
,SQLProcessUtilizationPercent
,SystemIdlePercent
,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilizationPercent
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 '%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC
-- Find currently executing queries
SELECT
r.session_id
,st.TEXT AS batch_text
,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
(
CASE
WHEN r.statement_end_offset = - 1
THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
ELSE r.statement_end_offset
END
) - r.statement_start_offset
) / 2 + 1) AS statement_text
,qp.query_plan AS 'XML Plan'
,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment