A CPU bottleneck can be caused by many factors such as lack of sufficient hardware resources, incorrect parallelism settings, hyper threading settings, inefficient queries , etc. However, the most common reason for excessive CPU utilization is bad query design. Query tuning, addressing any application design factors, and optimizing the system configuration can in most cases significantly reduce the CPU load. It is always prudent to identify the largest consumers of CPU bandwidth and see whether you can tune those queries or adjust the design/configuration factors before rushing out to buy faster and/or more processors.
I usually start with Performance Monitor as it provides one of the easiest means to determine whether the server is CPU bound. Generally looking at the Processor:% Processor Time counter with sustained values in excess of 80% of the processor time per CPU would generally deemed to be a bottleneck or CPU bound.
Alternately you can check CPU from within SQL Server by checking the DMVs. If you see a high number of requests waiting with the SOS_SCHEDULER_YIELD wait type or a high number of runnable tasks this can indicate that runnable threads are waiting to be scheduled and that there might be a CPU bottleneck on the processor.
The following query gives you a high-level overview of the currently cached batches or procedures are using the most CPU.
select top 50
sum(qrystats.total_worker_time) as total_cpu_time,
sum(qrystats.execution_count) as total_execution_count,
count(*) as number_of_statements,
group by qrystats.plan_handle
order by sum(qrystats.total_worker_time) desc
total_cpu_time total_execution_count number_of_statements plan_handle
1407639647 10 10 0x05000100A057C76040A3E4AE0B0000000000000000000000
789159179 8214 4 0x050012003FD94B5B40E33965060000000000000000000000
680179666 198 3 0x0500120002087D7A4023B08E050000000000000000000000
282844733 464895 1 0x05001800B00CAF0D4003C8170C0000000000000000000000
231823230 139894 2 0x050018005B798B104003E499050000000000000000000000
209812507 1044 1 0x0600230014471C004043B4F4010000000000000000000000
To resolve the query plan use the following code:
FROM sys.dm_exec_text_query_plan (0x05000100A057C76040A3E4AE0B0000000000000000000000,0,-1);
Retrieving query plan information about the top five queries by average CPU time
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], Plan_handle, query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1) ORDER BY total_worker_time/execution_count DESC;
Retrieving query text about the top five queries by average CPU time
SELECT TOP (5)
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN ‘– see objectText column–‘
ELSE ‘– query –‘ + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
END ) – deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY 3 DESC ;
With the above mentioned tools and queries you can quickly determine what is consuming your CPU and begin the task of query tuning.
As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this is helpful.
Next Post: (Part 3: CPU – Excessive Query Compilations)