CPU | SQL Conjuror

SELECT TOP 50 [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count, [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000, [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count, [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000, qs.execution_count, [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count, [Total I/O] = total_logical_reads + total_logical_writes, Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.[text]) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 ), Batch = qt.[text], [DB] = DB_NAME(qt.[dbid]), qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp where qs.execution_count > 5 --more than 5 occurences ORDER BY [Total MultiCore/CPU time(sec)] DESC

The other day, I was asked by our Application Team Lead to check why the CPU usage is very high in the Production DB server. It remained consistently high throughout the day. He asked whether it is coming from SQL Server or other processes.

Below is a useful script to review the CPU usage history for last 256 minutes (This will work on SQL Server 2008 and up)

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));

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 [Event 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'%<SystemHealth>%') AS x

) AS y

ORDER BY record_id DESC OPTION (RECOMPILE);

Now, if the value in “Other Process CPU Utilization (%)” column is higher than “SQL Server Process CPU”, then you may ask your sys admins to investigate the cause. But in our case, it was indicated clearly that the cause of high CPU usage was coming from SQL Server Process. Below is the output of the script.

After seeing the results, I investigated from the SQL queries perspective and found out that there are few expensive queries that are causing high CPU usage. How did I find out? That would be the subject of another blog.

Facebook

Google

Twitter

Tag: CPU

SQL Server – Top Queries Consuming CPU

SQL Server – Get CPU Usage History for last 256 minutes

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud