Monitoring SQL Server CPU usage is crucial for ensuring optimal performance, as it reveals resource bottlenecks, aids in identifying inefficient queries, and enables timely adjustments to maintain system responsiveness.
TSQL Code to monitor CPU
we will develop t-sql code that will use a DMV that will find and save incrementally CPU usage based on time and save it in a table. Let us create a table that will save CPU usage data :
CREATE TABLE [dbo].[CPU_Usage]
(
[EventDateTime] [datetime] NULL,
[SQLCPU_Usage] [int] NULL,
[IdleCPU] [int] NULL,
[CPU_Percent] [int] NULL
)
Create Proc TSQL_Monitor_CPUasBEGIN–Insert CPU usage on Increment basis–Created by Chandan Sahu–Email – chandansqlexpert@gmail.comdeclare @Last_inserted_CPU_Last_datetime datetime = getdate()-1if exists (select top 1 1 from CPU_Usage)select top 1 @Last_inserted_CPU_Last_datetime = [EventDateTime] from CPU_Usage order by [EventDateTime] descDECLARE @ticks_ms BIGINTSELECT @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 sub2WHERE 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 timeORDER BY id DESCEnd
Automate CPU Monitoring
To automate continuous CPU monitoring, Go to SQL server agent > Create New job as Monitoring and call a TSQL Code (Stored procedure) which we gave name as “TSQL_Monitor_CPU” ,
Schedule this job to run at every 2 minutes.
Generate CPU usage Report
you can read the data from CPU_Usage table and as per your cutom need populate the data in reporting tool or Excel , here is above a sample report.