Month: August 2015

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.
  • /* 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.

CPUUtilizationHistory

  • 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.
Click On Above Image to Buy
Click On Above Image to Buy