//
you're reading...
SQL Server

Troubleshooting Performance Problems in SQL Server (Part 2: CPU – Top Consumers)

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,
qrystats.plan_handle
from
sys.dm_exec_query_stats qrystats
group by qrystats.plan_handle
order by sum(qrystats.total_worker_time) desc

 

Sample Results:

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:

USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x05000100A057C76040A3E4AE0B0000000000000000000000,0,-1);
GO

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;

GO

Retrieving query text about the top five queries by average CPU time

SELECT TOP (5)
total_worker_time ,
execution_count ,
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
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
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 ;
GO

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)

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: