Troubleshooting High CPU Issues Caused by SQL Server Process

High CPU is a common issue in many environments, caused by not having regular maintenance activities and for not being proactive as a DBA/Developer. High CPU is often directly not known, it is notified indirectly either by Application Team/Users complaining about very slow performance/Timeouts in Application. In some environments there can be some monitoring tools installed which monitor the server and raise alerts when there is High CPU identified.

When users complain about slow performance/Timeout, first thing we can quickly check when connected to the server is the CPU usage from the task manager. Below are some troubleshooting steps/approach to be followed when we notice High CPU usage.

Note: It is important to understand what is considered as a high CPU. Below are considered to be High CPU cases
– Is the CPU usage above 90% consistently for more than a minute and it continues to be like that?
– Do you have any baseline on an average what was the CPU usage of the server during peak business hours? and Is the current CPU usage very much higher than baseline?

– CPU usage %Processor Time on the server can be divided into two parts, %User Time Or %Privileged Time. We can monitor the user and privilege time usage using perfmon. These counters are present under Processor category in perfmon.

%Processor Time = %User Time + %Privileged Time

– If CPU User Time is high, then we need to figure out, if SQL Server is the cause of high CPU, use task manager/perfmon to see if SQL Server is causing high CPU or some other process. SQL server executables are

• SQL DB Engine: sqlservr.exe
• SQL Agent: sqlagent.exe
• SSAS: msmdsrv.exe
• SSRS : ReportingServicesService.exe

– If it’s not SQL Server, then identify the process causing high CPU from (Task Manager) and if you don’t know what this other process is, then search or check internally. Need to identify the culprit and stop it as SQL Server is still IMPACTED.

– If SQL Server is the one causing high CPU, is it the then confirm that by running below queries against respective versions of SQL Server

–For SQL Server 2008 or higher

declare @ts_now bigint 
 
select @ts_now = ms_ticks from 
 
sys.dm_os_sys_info 
 
select record_id, dateadd (ms, (y.[timestamp] -@ts_now), 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

– For SQL Server 2005

declare @ts_now bigint 
 
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info 
 
select record_id, 
 
dateadd(ms, -1 * (@ts_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

– Find out which query inside SQL Server is using lot of CPU. Use below script to get the top CPU consuming queries


SELECT [Spid] = er.session_Id,
[Database] = Db_name(er.database_id),
se.program_name,
se.[host_name],
se.login_name,
er.status,
er.command,
wait_type,
wait_time,
last_wait_type,
wait_resource,
blocking_session_id,
er.cpu_time,
er.reads,
er.total_elapsed_time,
[Individual Query] =
Substring (qt.text, er.statement_start_offset / 2,
( CASE
WHEN er.statement_end_offset = -1
THEN Len(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset ) / 2),
[Parent Query] = qt.text,
se.login_time
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions AS se
ON er.session_id = se.session_id
CROSS APPLY sys.Dm_exec_sql_text(er.sql_handle)AS qt
WHERE er.session_Id &amp;gt; 50 -- Ignore system spids.
AND er.session_Id NOT IN ( @@SPID ) -- Ignore this current statement.
order by cpu_time desc

– If the top CPU query is from maintenance task like reindex or update stats, let it run or if it interrupting the business then stop the maintenance job.

– If top CPU query is from application, then there is an opportunity to tune the query by creating appropriate indexes or by rewriting the query.

Hope this was helpful for you.

Happy Reading,
SQLServerF1 Team

 

Leave a Reply

Your email address will not be published. Required fields are marked *