CXPACKET wait types

Are you experiencing CXPACKET wait types?

There is a performance issue and user facing slowness during running there application,to find out check the wait type occuring in sql server. If you find out CXPACKET waittype utilizing >5 then there might be a SQL Server parallelism problem and you must reduce MAXDOP to 1.

If your OLTP queries follow best practices and are well indexed, they probably will never generate a parallel plan. This is because they are fast and access a small amount of rows.

If they are missing indexes or SQL overestimates cardinality, SQL might decide to do scans, sorts, hashes, spools etc. These iterators, among others, can go parallel to reduce execution time at the cost of system resources. These iterators are not bad and they do have their place. It just isn’t on OLTP type of queries most of the time.

So dropping the MAXDOP on an OLTP system to 1 probably won’t hurt much because most of the time there is an IO bottleneck.

If the CXPACKET wait types are a symptoms of poor indexing and row count estimation, it won’t help either. Whole books have been written on indexing, query tuning and there is a nice whitepaper on stats best practices to avoid estimation problems so I am not going to go into that.

Some recommendations on values for CXPACKET waits and when
parallelism is desirable depending if this is an OLTP system on this whitepaper

SQL Server 2005 Waits and Queues
http://www.microsoft.com/technet/pro...ts_queues.mspx


I have found some queries to identify whether the CXPACKET is a symptom or the cause.


select r.cpu_time , r.logical_reads, r.session_id into #temp
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id --and s.last_request_start_time=r.start_time
where is_user_process = 1

waitfor delay '00:00:01'

select substring(h.text,
(r.statement_start_offset/2)+1 ,
((case r.statement_end_offset when -1 then datalength(h.text) else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text,
r.cpu_time-t.cpu_time as CPUDiff ,
r.logical_reads - t.logical_reads as ReadDiff,
p.query_plan,
r.wait_type,
r.wait_time,
r.last_wait_type,
r.wait_resource,
r.command,
r.database_id,
r.blocking_session_id,
r.granted_query_memory,
r.session_id, r.reads, r.writes,
r.row_count, s.[host_name],
s.program_name,
s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
full outer join #temp as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
cross apply sys.dm_exec_query_plan(r.plan_handle) porder by 3 desc

drop table #temp

You notice that you have several rows with CXPACKET wait types. With this query, you click the xml show plan link and:
1. search the xml for missing indexes.
2. Save as a .sqlplan and reopen in SSMS
3. Compare estimated and actual rows in the iterators on the right side of the plan. Poor estimates may bubble to the left as well.
4. If there are no missing indexes, estimates and actual are fairly close, reducing MAXDOP may help if it is not a huge report or query.

5. If there are missing index or bad estimations, fix it!


One thing to note, if you do turn down MAXDOP server-wide, turn in on at the query level(enterprise edition) on your index operation because they are optimized for it.
Alter index all on tblBlah rebuild with (maxdop=32);