Saving CPU information with a SQL Server SQL Agent Job.

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

CPU query

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],

My First Post

I wanted to start this blog to share some of the things that I encounter day to day as a production DBA.  It will also help me remember some of the things that I have done so I don’t have to reinvent the wheel the next time I have to do this.

I hope this blog will be a help to you.  I figure that if it was something that I encountered, someone else might have as well.

David Hutton