High CPU usage for long times may damage the server’s performance and shorten the server’s life. If you notice the SQL Server Service consumes too much CPU it is important to analyze the reasons for that and try to schedule them to less CPU consuming time frames or optimize the queries’ performance. In this article I’ll focus on finding the most CPU consuming queries.
When you see high CPU usage of the SQL Server Service in the Task Manager you could use the following query in order to find the highest CPU consuming queries and focus on them in order to solve the problem:
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 above query may return up to 50 results and there’s no reason why you’d need more than that, in most cases handling the top 10 would decrease the CPU consumption dramatically.
Keeping the CPU usage reasonable in the long term extends the Server’s lifetime and prevents bottlenecks of system services.