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.
--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;