Configure alerts for CPU utilization higher than 80% for more than 5mins
SET NOCOUNT ON DECLARE @TimeNow bigint SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info -- Collect Data from DMV Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]), GetDate())EventTime, SQLSvcUtilization, SystemIdle, (100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords from ( select record.value('(./Record/@id)[1]', 'int')record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization, timestamp from ( select timestamp, convert(xml, record)record from sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%<SystemHealth>%')x )y order by record_id desc To send detailed sql server session reports consuming high cpu For a dedicated SQL Serve...