Get CPU utilization history in SQL Server

Note: Best viewed in Internet Explorer 6 and above

Implementation: How to get CPU utilization history in SQL Server.

Scenario: Lets find out the CPU Utilization history based on minutes/hours.

Infrastructure:The below query/procedure has been run/tested successfully on sql server 2008 R2.

Steps:

Create the table from using below query in any database-One time table creation.

USE [master]
GO
/****** Object: Table [dbo].[Cpu_LastOnehour] Script Date: 08/22/2015 23:28:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cpu_LastOnehour](
[SQL Server Process CPU Utilization] [int] NULL,
[System Idle Process] [int] NULL,
[Other Process CPU Utilization] [int] NULL,
[Event Time] [datetime] NULL
) ON [PRIMARY]
GO

Kindly download above table script from below link.

https://app.box.com/s/e3g5pilky6rqq359w3bhdo6forbjy70c

/* create the procedure on respective DB.(One time stored procedure creation)

USE [master]
GO
/****** Object: StoredProcedure [dbo].[Usp_Cpu_UtilizationLastOneHour] Script Date: 08/22/2015 23:28:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— Get CPU Utilization History for last 60 minutes (SQL 2008)
Create Proc [dbo].[Usp_Cpu_UtilizationLastOneHour]
as
begin
DECLARE @ts_now bigint
set @ts_now= (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(60) 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] –where -1 is the cpu in different of one minutes.
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;
end
GO

You can download the above procedure from link given above.
Now put the below code in SQL job and run it as per your need.

insert into Cpu_LastOnehour
exec Usp_Cpu_UtilizationLastOneHour
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @subject varchar(250)
select @subject = ‘Cpu Utilization For Last One Hour For Server’ + @@servername
SET @tableHTML =
N'<H1 bgcolor=#A52A2A>Cpu Utilization For Last One Hour </H1>’ +
N'<table border=”1″>’ +
N'<tr bgcolor=#6495ED><th>SQL Server Process CPU Utilization</th><th>System Idle Process</th>’ +
N'<th>Other Process CPU Utilization</th><th>Event Time</th></tr>’ +
CAST ( ( SELECT td = [SQL Server Process CPU Utilization], ”,
td = [System Idle Process], ”,
td = [Other Process CPU Utilization], ”,
td=[Event Time],”
from Cpu_LastOnehour
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBA’,
@recipients=’dharmesh@xyz.com’,
@subject =@subject,
@body = @tableHTML,
@body_format = ‘HTML’ ;
truncate table Cpu_LastOnehour
go

You can download the above procedure from link given above.
Once you are done with this you will get the output like below.Please check the below screen shot for the same.

This is how you can get CPU Utilization history in sql server.
Please do comments when you find this useful or any suggestion on the same will be highly accepted.