Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

A closer look at CXPACKET wait type in SQL Server


By:   |   Read Comments (7)   |   Related Tips: More > Performance Tuning

Next Free Webcast - The more things change… DBAs versus Sysadmins in cloud availability


Problem

If you have a server that has more than one CPU core, you may experience high values of CXPACKET wait types. This is typically due to queries that run in parallel and the real issue is to understand how different versions of a query can impact CXPACKET waits. In this tip we examine how changing a query can impact CXPACKET waits.

Solution

This goal of this tip is to increase query performance, decrease high CXPACKET waits without reducing MAXDOP. We will look at a few examples of a query and the impact on CXPACKET waits.

If you are not familiar with CXPACKET, you can learn more here http://msdn.microsoft.com/en-us/library/ms179984.aspx.

These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start. Let's see it with the following examples. Please note that all the examples below have been provided by SQL Work Shops.

SQL Server CXPACKET Wait Type Demonstration

First we will create a new table.

SET nocount ON 
CREATE TABLE orders (d_id INT, o_id INT, o_amount INT, o_description CHAR(2000)) 
CREATE UNIQUE CLUSTERED INDEX test ON orders(d_id, o_id)

Then insert 800,000 rows.

BEGIN TRAN 
DECLARE @i INT 
SET @i = 1 
WHILE @i <= 800000 
BEGIN 
INSERT INTO orders VALUES (@i % 8, @i, RAND() * 800000, REPLICATE('a', 2000)) 
SET @i = @i + 1 
END 
COMMIT TRAN

Then update the statistics with a full scan, so the optimizer can work easier.

UPDATE STATISTICS orders WITH fullscan 
GO 
CREATE TABLE #department (d_id INT) 
INSERT INTO #department VALUES(0) 
INSERT INTO #department VALUES(1) 
INSERT INTO #department VALUES(2) 
INSERT INTO #department VALUES(3) 
INSERT INTO #department VALUES(4) 
INSERT INTO #department VALUES(5) 
INSERT INTO #department VALUES(6) 
INSERT INTO #department VALUES(7) 
GO 

Then before we begin, enable statistics time to observe CPU time and elapsed time. Include the actual execution plan, clear the wait stats and make sure the system you are using is idle otherwise, the CXPACKET will not reflect our query waits alone. The server I tested on has 2 quad core CPUs for a total of 8 cores.

SQL Server CXPACKET Test 1 - Run query using 1 processor (MAXDOP 1)

SET STATISTICS time ON 
GO 
DBCC sqlperf('sys.dm_os_wait_stats', clear)
 
DECLARE @order_amount INT 

SELECT @order_amount = MAX(o_amount) 
      FROM orders o INNER JOIN #department d ON (o.d_id = d.d_id) 
      OPTION (maxdop 1)
 
SELECT * FROM sys.dm_os_wait_stats 
      WHERE wait_type = 'CXPACKET'

SQL Server Execution Times:  
   CPU time = 688 ms,  elapsed time = 689
SQL Server cxpacket wait type

As we might notice the CPU time is very close to elapsed time. The query is CPU bound; if not execute the query again to make sure we are reading the data from cache. This example is to understand parallel query execution, not disk IO, so we need to get the data in the cache before we can continue.

We executed the query with MAXDOP 1 that is why the CXPACKET wait time is 0 because the query executes serially.

SQL Server CXPACKET Test 2 - Let's run it again in parallel (MAXDOP 0)

DBCC sqlperf('sys.dm_os_wait_stats', clear)

DECLARE @order_amount INT 

SELECT @order_amount = MAX(o_amount) 
FROM orders o INNER JOIN #department d ON (o.d_id = d.d_id) 
OPTION (maxdop 0) 

SELECT * FROM sys.dm_os_wait_stats 
WHERE wait_type = 'CXPACKET' 
GO 

SQL Server Execution Times: 
   CPU time = 874 ms,  elapsed time = 221 ms. 

On the execution plan, right click on Cluster Index Seek and click on Properties as shown below. We can see that SQL Server had distributed 100,000 rows across each of my 8 CPUs. Notice the CPU time and elapsed time. Elapsed time (221ms) is similar to CXPACKET wait time (234 ms) as no child threads are entirely idle and the coordinator in this case waits for all child threads to complete processing the rows with CXPACKET wait type.

SQL Server cxpacket using all threads
SQL Server cxpacket wait types

SQL Server CXPACKET Test 3 - Let's drop and recreate our #department table and only insert 4 rows this time.

DROP TABLE #department 
--insert into #department values (0) 
INSERT INTO #department VALUES (1) 
--insert into #department values (2) 
INSERT INTO #department VALUES (3) 
--insert into #department values (4) 
INSERT INTO #department VALUES (5) 
--insert into #department values (6) 
INSERT INTO #department VALUES (7) 

DBCC sqlperf('sys.dm_os_wait_stats', clear)
 
DECLARE @order_amount INT 

SELECT @order_amount = MAX(o_amount) 
FROM orders o INNER JOIN #department d ON (o.d_id = d.d_id) 
OPTION (maxdop 0) 

SELECT * FROM sys.dm_os_wait_stats 
WHERE wait_type = 'CXPACKET' 
GO 

SQL Server Execution Times: 
   CPU time = 454 ms,  elapsed time = 105 ms. 

The CXPACKET wait time should be approximately 4 times the elapsed time and CPU time should be approximately 4 times the elapsed time. The reason is only 4 threads are processing the rows, 100,000 rows each (4 threads X elapsed time = CPU time), the other 5 threads (4 child threads and the coordinator thread) wait with CXPACKET wait time (5 threads X elapsed time = CXPACKET wait time).

SQL Server cxpacket using only a few threads
SQL Server cxpacket wait types

SQL Server CXPACKET Test 4 - Let's now re-write our query using an IN instead of an INNER JOIN

DROP TABLE #department 
CREATE TABLE #department (d_id INT) 
INSERT INTO #department VALUES (0) 
INSERT INTO #department VALUES (1) 
INSERT INTO #department VALUES (2) 
INSERT INTO #department VALUES (3) 
INSERT INTO #department VALUES (4) 
INSERT INTO #department VALUES (5) 
INSERT INTO #department VALUES (6) 
INSERT INTO #department VALUES (7) 
GO 

DBCC sqlperf('sys.dm_os_wait_stats', clear)
 
DECLARE @order_amount INT 

SELECT @order_amount = MAX(o_amount) 
FROM orders o WHERE d_id IN (SELECT d_id FROM #department) 
OPTION (maxdop 0) 

SELECT * FROM sys.dm_os_wait_stats 
WHERE wait_type = 'CXPACKET' 
GO 

SQL Server Execution Times: 
   CPU time = 860 ms,  elapsed time = 212 ms.

If you take a look at the execution plan, we can see that not all threads processed 100,000 rows. Some threads processed 200,000 rows, some 100,000 rows, and some none. This is the reason for high CXPACKET wait time (due to idle threads waiting on CXPACKET) and higher elapsed time (uneven distribution of rows).

SQL Server cxpacket using only some of the threads
SQL Server cxpacket wait types

SQL Server CXPACKET Test 5 - Let's try this final example without using the temporary table

DBCC sqlperf('sys.dm_os_wait_stats', clear)
 
DECLARE @order_amount INT 

SELECT @order_amount = MAX(o_amount) 
FROM orders o WHERE d_id IN (0,3,5,7) 
OPTION (maxdop 0) 

SELECT * FROM sys.dm_os_wait_stats 
      WHERE wait_type = 'CXPACKET' 
GO 

SQL Server Execution Times: 
   CPU time = 578 ms,  elapsed time = 72 ms. 

Finally we can see that in the above query, all threads are processing rows, leading to low elapsed time. Since no child threads were entirely idle, elapsed time is similar to CXPACKET wait time as only the coordinator thread waited with CXPACKET wait type.

SQL Server cxpacket using all threads for query plan

Summary

In all the above examples, join, subquery and IN, when handling 4 rows from a temporary table you will see the elapsed time to be less than with an IN clause. This does not mean that an IN clause is always better. This is not a discussion about efficiency of IN clause or joins or subqueries, but about how to tune certain parallel queries for shortest elapsed time and to see and understand how to interpret the SQL Server CXPACKET wait type.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Monday, June 13, 2016 - 6:34:32 AM - Erez Ben Simon Back To Top

 Great tip!

 

Thanks a lot!

Erez

 


Tuesday, June 18, 2013 - 9:29:04 AM - Sudhakar Back To Top

Thanks for sharing this wonderfull article. This article helped me to emerge the reason for high utilization.


Tuesday, June 11, 2013 - 6:08:51 PM - Srinath Back To Top

Excellent post !!


Friday, December 16, 2011 - 9:02:33 AM - Matteo Back To Top

Question: when you see CXPACKET acting up on production server, what do you do?

You will have to review your MAX DOP setting, please check this post http://blogs.msdn.com/b/cindygross/archive/2011/01/28/the-ins-and-outs-of-maxdop.aspx; and take a closer look to your queries.


Wednesday, December 14, 2011 - 6:29:05 PM - Jason Back To Top

Bill, Cranfield, when you see CXPACKET acting up on production server, what do you do?


Wednesday, September 01, 2010 - 1:20:04 PM - Bill Back To Top
Good stuff, thanks for the article.


Friday, June 18, 2010 - 4:52:51 AM - --cranfield Back To Top

this is is a great tip. Thanks for posting.  I have learned something new today.


Learn more about SQL Server tools