1.3K
High CPU utilization is a common issue for SQL Server environments that can affect database performance and cause system instability. As a database professional, it’s crucial to have a reliable method for quickly checking and monitoring CPU utilization. This blog post will show you how to do just that using two straightforward methods: checking the task manager and using a simple T-SQL script. So, start monitoring your SQL Server’s CPU utilization now and ensure it’s performing at its best!
As you can see in the example image below, in the CPU process tab, the SQL Server Windows NT – 64 Bit has the most resources consumed (89.2%).
Using the Task Manager
For T-SQL, you can use the Ring Buffer (sys.dm_os_ring_buffers) and Dynamic Management Views (sys.dm_os_sys_info) to check the CPU utilization of the SQL Server and other processes.
T-Sql
-----------------------------------------------------------------------
-- This script is to monitor CPU utilization using Ring Buffer and DMVs
-----------------------------------------------------------------------
DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT
record_id
, dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
, SQLProcessUtilization
, SystemIdle
, 100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
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
/* The script above uses the Ring Buffer (sys.dm_os_ring_buffers) and Dynamic Management Views (sys.dm_os_sys_info)
to check the CPU utilization of the SQL Server and other processes. */
/* To use this script, simply copy and paste it into a new query window in SQL Server Management Studio and execute it.
The results will show the event time, SQL Process Utilization, System Idle, and Other Process Utilization. */
/* You can monitor the CPU utilization by observing the SQLProcessUtilization column, which represents the percentage of CPU utilization by the SQL Server process.*/
/* Make sure to monitor your CPU utilization regularly to detect and troubleshoot any performance issues that may affect the SQL Server environment. */
