Understanding CXPACKET wait stats in SQL Server

Understanding CXPACKET

From the SQL BOL (Books on Line) CXPACKET is defined as Class Exchange Packet and “occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.”
To get a deeper understanding, it can be explained this way. Let’s say you need to run a single query and you will need a group of threads to process this query. SQL Server will assign each thread a task from the query. Some of the threads will finish faster and others will lag behind. The threads that finish or execute faster will then tell the lagging threads that they’re done with their assigned task hence, they are waiting. The CXPACKET wait stat is flagged by the threads who finished earlier and not by the threads that are lagging behind.
To be clear, CXPACKET wait stats does not exactly pose a problem. But it can be an alert or warning that something needs to be tuned up in the running query. Or you can check if you need to add the OPTION (MAXDOP 1) hint when running your query.

Parallel execution can be a highly effective way of dealing with large sets of data, say in data warehousing or reporting server scenario. But in OLTP systems, this is not as effective and can have a negative impact on performance.

One query that I use often when I my server is having too many CXPACKET wait stats is pasted below. You can fine tune this to suit your requirements and also add hint for OPTION (MAXDOP 1)

SELECT TOP 10 st.text
,st.dbid
,st.objectid
,qs.total_worker_time
,qs.last_worker_time
,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

Important points for CXPACKET wait type and parallelism

• OLAP/reporting related queries benefits from parallelism but not OLTP systems.
• Look at your plan cache, which of your queries use parallelism?
• Check query plans – do you have missing indexes?
• When was the last time you ran sp_updatestats?
• What other waits are occurring that might be causing CXPACKET?
• Consider using MAXDOP to fine tune specific queries. Again test this first in a staging environment.
• Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
• Do not adjust max degree of parallelism without proper analysis of your production servers workload and queries. Make this your last resort!

About these ads

2 responses to “Understanding CXPACKET wait stats in SQL Server

  • matt.bowler

    Good post. Might be worth pointing out that all parallel queries include a ‘coordinator’ thread that is used for synchronization. This thread doesn’t do any of the query processing and is accumulating CXPACKET waits from the start of the query run – even if there is no problem with the execution. This explains why you can often see CXPACKET waits on a system, and they are not necessarily a bad sign. The key is looking at how fast these waits accumulate, and comparing to a baseline.

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

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 48 other followers