CREATE TABLE [dbo].[CPU_Usage]( 
	[EventDateTime] [datetime] NULL,
	[SQLCPU_Usage] [int] NULL,
	[IdleCPU] [int] NULL,
	[CPU_Percent] [int] NULL 
)


Create Proc TSQL_Monitor_CPU
as
BEGIN
--Insert CPU usage on Increment basis  
--Created by Chandan Sahu
--Email - chandansqlexpert@gmail.com

	declare @Last_inserted_CPU_Last_datetime datetime = getdate()-1
	if exists (select top 1 1 from CPU_Usage) 
	select top 1  @Last_inserted_CPU_Last_datetime = [EventDateTime] from CPU_Usage order by [EventDateTime] desc

	DECLARE @ticks_ms BIGINT  
	SELECT @ticks_ms = ms_ticks FROM sys.dm_os_sys_info; 

	INSERT INTO [CPU_Usage]
	SELECT dateadd(ms, -1 * (@ticks_ms - [timestamp]), GetDate()) AS EventDateTime   , 
		 ProcessUtilization as 'SQLCPU_Usage', 
		 SystemIdle 'IdleCPU',
		 100 - SystemIdle - ProcessUtilization AS 'CPU_Percent' 
		 FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS id, 
		 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
		 AS ProcessUtilization, 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 sub1) AS sub2  
	WHERE dateadd(ms, -1 * (@ticks_ms - [timestamp]), GetDate()) > dateadd(ms,200,@Last_inserted_CPU_Last_datetime) 
	--added 200ms time to avoid getting cpu record repeatition on same time
	ORDER BY id DESC
End