I had a requirement to save the CPU data on a server. You know managers like to have fancy charts. I know that SQL Server saves the last 256 minutes worth of CPU data and I obtained a script from Glenn Berry (https://www.sqlskills.com/about/glenn-berry/) that would give me that information. If you are not familiar with Glenn’s DMV scripts you should check them out.
—start of code
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(256) 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 [Date and Time]
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 WITH (NOLOCK)
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE N’%%’) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
That will give you the last 256 minutes of CPU usage.
—end of code
I created a database called CPU and a table called CPUData (I am not real original about names)
Here is the table, I assume you can create a database in SQL Server.
—start code
USE [CPU]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CPUDATA](
[SQL Server Process CPU Utilization] [int] NULL,
[System Idle Process] [int] NULL,
[Other Process CPU Utilization] [int] NULL,
[Date and Time] [datetime] NULL
) ON [PRIMARY]
GO
—end of code
I then modified Glenn’s script to insert the last record into my table in a new SQL Server job that I set to run once a minute.
—Start of code
SET QUOTED_IDENTIFIER ON –added this so could run this as a job
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
insert into CPU.dbo.CPUDATA
SELECT TOP (1) 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 [Date and Time]
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 WITH (NOLOCK)
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE N’%%’) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
–I also loaded the last 256 minutes of data into my table to get things started – run this in QA:
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
insert into CPU.dbo.CPUDATA
SELECT TOP (1) 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 [Date and Time]
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 WITH (NOLOCK)
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE N’%%’) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
—end of code
And now I just let my job run every minute and when I want to collect the data I will just run this:
select * from CPU.dbo.CPUDATA order by [Date and Time] DESC
I thought about running my job just once an hour or two and changing it to top 60 or 120 and I might but for now I am done.
if you wanted to run the script every two hours you would change this line in the SQL job.
SELECT TOP (120) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
MySQLJourney 