CXPACKET - Parallellism

This is commonly where a query is parallelized and the parallel threads are not given equal amounts of work to do. One thread may have a lot more to do than the others, and so the whole query is blocked while the long-running thread completes. If this is combined with a high number of PAGEIOLATCH_XX waits, it could be large parallel table scans going on because of incorrect non-clustered indexes, or out-of-date statistics causing a bad query plan. If neither of these are the issue, you might want to try setting MAXDOP to 1 for the offending queries (or possibly the whole instance).

MAXDOP can be set at two places:

1. At server side:

EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

2. At Query level:

Using Option (MAXDOP 2) at the end of the query.

Important point to be noted here is that the serve rlevel configuration can be overwritten at query level.

Interpretation of parallelism is very important as it can cause a delay , ofcourse even speed up as well. Very often parallelsim cause issues on OLTP systems.There are several area where parallelism may give you a better results like rebuilding indexes, update statistics ect. So it is always better to evaluate the situation and disable the parallelism at server side. Mainly parallelism is occuring because of one of the following:

1. Query is not a pure OLTP query.

2. No proper index.

3. No proper filter conditions in query.

Useful Queries

--**********************Finding out the currently running parallel queries in the server Starts **********

SELECT r.session_id

,r.request_id

,MAX(ISNULL(exec_context_id, 0)) as number_of_workers

,r.sql_handle

,r.statement_start_offset

,r.statement_end_offset

,r.plan_handle

FROM

sys.dm_exec_requests r

JOIN sys.dm_os_tasks t

ON r.session_id = t.session_id

join sys.dm_exec_sessions s

ON r.session_id = s.session_id

WHERE s.is_user_process = 0x1

GROUP BY r.session_id, r.request_id, r.sql_handle, r.plan_handle, r.statement_start_offset,

r.statement_end_offset

HAVING MAX(ISNULL(exec_context_id, 0)) > 0

--**********************Finding out the currently running parallel queries in the server Ends **********
--**********************Finding out all parallel queries in server(using cached plans) *******************Starts

select

p.*,

q.*,

cp.plan_handle

from

sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_query_plan(cp.plan_handle) p

cross apply sys.dm_exec_sql_text(cp.plan_handle) as q

where

cp.cacheobjtype = 'Compiled Plan' and

p.query_plan.value('declare namespace

p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

max(//p:RelOp/@Parallel)', 'float') > 0

--**********************Finding out all parallel queries in server(using cached plans*******************Ends