Recent CPU Utilization History (SQL 2008/2008 R2 and 2012 Only)
Get CPU Utilization History for last 256 minutes (in one-minute interval)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info);
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
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 WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
output
======
or
/* Variables */
DECLARE
@StartTime DATETIME = '01/01/1900 00:00:00'
,@EndTime DATETIME = '01/01/2999 23:59:59'
,@ShowDetails BIT = 1 -- 1 = True, 0 = False
/* Script Begin */
-- Find the timestamp for current server time
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info;
;With CPU_RingBuffer_Snapshot as
(
SELECT
record_id
,DATEADD(ms, -1 * (@ts_now - TIMESTAMP), GETDATE()) AS EventTime
,SQLProcessUtilization
,SystemIdle
,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
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 '% %'
AND DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) BETWEEN @StartTime AND @EndTime
) AS x
) AS y
)
SELECT record_id,EventTime,SQLProcessUtilization,SystemIdle,OtherProcessUtilization,
AVG(SQLProcessUtilization)Over() 'Avg CPU Utilization',
MIN(SQLProcessUtilization) Over() 'Min CPU Utilization',
MAX(SQLProcessUtilization) Over() 'Max CPU Utilization',
Min(EventTime)Over() 'Min Sample DateTime',
MAX(EventTime)Over() 'Max Sample Datetime'
FROM CPU_RingBuffer_Snapshot
Then using below query you can check-- TOP 50 cached plans that consumed the most cumulative CPU
Run the following query to get the TOP 50 cached plans that consumed the most cumulative CPU
--All times are in microseconds
qs.total_elapsed_time, qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads, qs.total_physical_reads,
qs.max_physical_reads,t.[text], qp.query_plan, t.dbid, t.objectid, t.encrypted, qs.plan_handle, qs.plan_generation_num
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC
How to get queries running at a specific time
----------------------------------------------------------------------------------------
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = --Converted from microseconds
qs.total_worker_time/1000000,
qs.last_execution_time,
average_CPU_inSeconds = --Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = --Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
where qs.last_execution_time between '2018-03-22 03:38' and '2018-03-22 04:52'
ORDER BY qs.total_worker_time DESC
Then look into the execution plan of the query and accordingly suggest the customer
Thanks For reading.
