Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
During PASS we announced changes to how users will be able to make parallelism waits more actionable – specifically the “infamous” CXPACKET. This change effectively splits CXPACKET waits into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER). This change will be effective starting with SQL Server 2017 CU3 and upcoming SQL Server 2016 SP2 (this wait type is already present in Azure SQL Database).
It’s important to mention a couple notes before getting into details:
It’s also important to understand that parallelism is implemented as if it were two operators. There’s the producers that push data to consumers, and the consumers that may have to wait for data from producers.
And so it becomes easier to understand how producer waits are the ones that may require attention, while consumer waits are inevitable as a passive consequence of longer running producers.
With this split, CXPACKET occurs when trying to synchronize the query processor exchange iterator or getting a required buffer.
And CXCONSUMER occurs when a consumer thread waits for a producer thread to send rows.
These wait types will be surfaced at the server level with sys.dm_os_wait_stats, database level with sys.query_store_wait_stats, and session level with sys.dm_exec_session_wait_stats DMVs. The same for sys.dm_os_waiting_tasks, and the WAIT_INFO and WAIT_COMPLETED xEvents.
However, because CXCONSUMER is not actionable directly, this wait will not surface in the Performance Dashboard nor in Showplan.
Take the following simple query running in AdventureWorks2016:
SELECT *
FROM Sales.SalesOrderDetail SOD
INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
WHERE SalesOrderDetailID > 10
ORDER BY Style
With the resulting showplan:
Since we have wait stats and query time stats in the root node of showplan, let’s see how they look:
A lot of CXPACKET waits, totaling over 31s, for 1.2s CPU and 2.8s overall elapsed execution time. Also notice from showplan, this query is executing with DOP 12:
As mentioned above, both producer and consumer waits are tracked at the server and session level, so let’s look at sys.dm_exec_session_wait_stats also. Note that any change that is done to improve on the high waits scenario above is aimed at producer waits (CXPACKET), given consumer waits (CXCONSUMER) may inevitably exist a normal part of parallelism:
Ok, so actually most of the parallelism waits are producer waits. We may expect to see consumer waits to go up in the initial moments of parallel query execution, but then stabilize.
A quick way to handle this scenario is to reduce DOP using a hint:
SELECT *
FROM Sales.SalesOrderDetail SOD
INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
WHERE SalesOrderDetailID > 10
ORDER BY Style
OPTION (MAXDOP 4)
In the resulting plan, here are the wait stats and query time stats:
CXPACKET waits down to just over 10s, for 0.8s CPU and 2.7s overall elapsed execution time. So while the elapsed time remained very similar (although lower), there were gains in reducing CPU usage and waits (3x lower) by adjusting DOP just enough.
Your mileage may vary depending on your setup, but thinking that this query may run at scale in a given environment, the gains are relevant. And were made possible by all the insights unlocked in showplan.
Pedro Lopes (@sqlpto) – Senior Program Manager
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in