Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
CPU 100% - Check for Parallel Queries

25 April,2013 by Jack Vamvas

If CPU is 100% for extended periods and there are performance issues– check for Parallel queries.

This quick and dirty check does not replace an indepth root cause analysis , but it does give you some quick feedback . Use the feedback to take action and then focus on fixing the bottleneck

The CXPACKET wait type occurs when more than one CPU core exists. Queries will run in parallel  and the waits will occur when one part of the query is waiting for another thread to complete it’s workload.

Read more on SQL Server - Waitstats CXPACKET wait type and how to reduce it

 

--Parallel running tasks – with the multiple active requests 
select * from sys.dm_os_tasks as t 
where t.session_id in ( 
select t1.session_id 
from sys.dm_os_tasks as t1 
group by t1.session_id 
having count(*) > 1 
and min(t1.request_id) = max(t1.request_id)); 

--Parallel  Requests –returning detailed information about executed requests 
select * 
from sys.dm_exec_requests as r 
join ( 
select t1.session_id, min(t1.request_id) 
from sys.dm_os_tasks as t1 
group by t1.session_id 
having count(*) > 1 
and min(t1.request_id) = max(t1.request_id) 
) as t(session_id, request_id) 
on r.session_id = t.session_id 
and r.request_id = t.request_id;

 

Read More

Waitstats CXPACKET wait type and how to reduce it - SQL Server DBA

Datawarehouse Wait Stats - SQL Server DBA

SQL Server – More processor speed or cpu cores - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer