-- 1.1. Determine the CPU usage
--2. Take snapshots of query running for next 5mins
--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 '%
--order by timestamp desc
/*
select * from test.dbo.sql_process_snapshot
drop table sql_process_snapshot
create table sql_process_snapshot (
logdatetime datetime,
log_comment nvarchar(128),
session_id smallint,
status nvarchar(24),
command nvarchar(24),
cpu_time int,
total_elapsed_time int,
start_time datetime,
database_name nvarchar(256),
query_text nvarchar(1024)
)
*/
declare
@ts_now bigint,
@sql_cpu_usage int,
@server_cpu_usage int,
@threshold_cpu_value int,
@snapshot_delay varchar(8),
@snapshot_iterations tinyint
select
@threshold_cpu_value = 90,
@snapshot_delay = '00:00:15', --hh:mm:ss
@snapshot_iterations = 16 --cpu usage is recorded every 4mins
select
@ts_now = cpu_ticks/(cpu_ticks/ms_ticks)
from sys.dm_os_sys_info;
;with cpu_usage
as
(
select top 1
record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as event_time,
sql_process_utilization,
system_idle,
100 - (system_idle + sql_process_utilization) as server_process_utilization
from
(
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as system_idle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as sql_process_utilization,
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 '%
) as x
) as y
order by event_time desc
)
select
@sql_cpu_usage = sql_process_utilization,
@server_cpu_usage = server_process_utilization
from cpu_usage
select @sql_cpu_usage as 'SQL CPU Usage', @server_cpu_usage as 'Server CPU Usage'
if @sql_cpu_usage >= @threshold_cpu_value
begin
declare @i int
select @i = 1
while @i <= @snapshot_iterations begin insert into test.dbo.sql_process_snapshot(logdatetime, log_comment, session_id, [status], command, cpu_time, total_elapsed_time, start_time, database_name, query_text) select getdate(), N'SQL exceeded CPU threshold: SQL CPU Usage - ' + convert(nvarchar(10),@sql_cpu_usage) + N'; Server CPU Usage - ' + convert(nvarchar(10),@server_cpu_usage), req.session_id, req.[status], req.command, req.cpu_time, req.total_elapsed_time, req.start_time, db_name(req.database_id) as db_name, stmt.text from sys.dm_exec_requests req cross apply sys.dm_exec_sql_text(sql_handle) as stmt where session_id <> @@spid --exclude current process
waitfor delay @snapshot_delay
select @i = @i + 1
end
end
if @server_cpu_usage >= @threshold_cpu_value
begin
insert into test.dbo.sql_process_snapshot(logdatetime, log_comment)
select
getdate(),
N'Server exceeded CPU threshold: SQL CPU Usage - ' + convert(nvarchar(10),@sql_cpu_usage) + N'; Server CPU Usage - ' + convert(nvarchar(10),@server_cpu_usage)
end
---
create trigger tI_sql_process_snapshot
on sql_process_snapshot
for insert
as
begin
declare @log_event nvarchar(128)
select top 1 @log_event = log_comment from inserted
--exec sp_send_mail
end
go
--
