CXPACKET is one of the famous wait types that database administrators are experiencing. Before moving into the details of the CXPACKET wait type, first, let us discuss the waits in SQL Server in brief.
SQL Server is a mini operating system. When SQL Server is executing any task and if for any reason it has to wait for resources to execute the task, it will wait in a list until it gets the relevant resources. This list is called the Waiter list or suspended list. This is not a queue as whenever that task is ready with required resources it will move to the runnable queue which means that it is ready to execute whenever the processor is free to execute. Depending on the type of wait, there are more than 200 wait types. CXPACKET, WRITELOG, ASYNC_NETWORK_IO are the most common wait types. This is a very brief discussion about waits. For any case where this is not clear, it will be better to get more details from other sources as this article is not intended to discuss waits in detail.
The CXPACKET wait type is the one of the most common wait type if not the most. According to the survey done at sqlskils.com, out of the waits that are occurring in the surveyed systems, more than 25% are CXPACKET wait types. That tells you how frequent CXPACKET wait type is.
CXPACKET occurs when a parallel operation is created for a task. The query processor may determine that a particular operation can be performed more efficiently by using multiple threads depending on the cost of the query. At the execution time, the query execution portion of the query processor decide what parallelism to use. For example, if you are executing a query which deals with a large portion of a table, multiple threads are required. This is a common phenomenon when queries running in a fact table in a data warehouse system as typical query in a fact table needs a large portion of the data to be extracted. After identifying the number of threads needed to execute the query, it will decide which portion to be executed by which thread. Ideally, load will be divided equally among the threads. This scenario is displayed in the following diagram:
| 1 | 2 | 3 | 4 | |
|
Controlling Thread (Thread 0) | Waiting ( CXPACKET Wait Type) | |||
| Thread 1 | EXECUTING | |||
| Thread 2 | EXECUTING | |||
| Thread 3 | EXECUTING | |||
| Thread 4 | EXECUTING | |||
Let us assume that blue part is the table which needs to be scanned. SQL Server has divided scanning operation to four parts and each part is assigned to a thread. As you can see, thread 1 is assigned to a part one of the table etc. There will be a control thread which will be overlooking the other executing threads. Control thread will be seen as CXPACKET while other threads are seen as executing or running.
There can be another scenario for the CXPACKET wait type. Though the entire load is divided among threads so that cost will be equal, there can be cases where one or more threads are still executing while the other threads have completed. Until the last thread is executed, other completed threads are on CXPACKET wait. Following diagram is to elaborate the given scenario:
| 1 | 2 | 3 | 4 | |
|
Controlling Thread (Thread 0) | CXPACKET | |||
| Thread 1 | EXECUTED | CXPACKET | ||
| Thread 2 | EXECUTED | CXPACKET | ||
| Thread 3 | EXECUTED | CXPACKET | ||
| Thread 4 | EXECUTING | |||
CXPACKET scenario is equivalent to a scenario where you have given lots of current notes to count to a bank cashier. Since one cashier cannot count all the notes alone, he will distribute it among his fellow workers. In case, one cashier is still counting while the others have completed the counting, still all the cashiers have to wait until the last count is known.
When a CXPACKET is going on, there are couple of ways to identify it. First one from the query plan, as shown below:
In the query plan, there is a yellow colored circle in the controls, which denotes that query is on parallelism.
Another way of identifying the CXPACKET wait type is by querying sys.dm_os_waiting_tasks DMV as shown in below query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT owt.session_id, owt.exec_context_id, owt.wait_duration_ms, er.command, owt.wait_type, owt.blocking_session_id FROM sys.dm_os_waiting_tasks owt INNER JOIN sys.dm_exec_sessions es ON owt.session_id = es.session_id INNER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id WHERE es.is_user_process = 1 --and es.session_id = 400 ORDER BY owt.session_id, owt.exec_context_id |
Above query will return the following output:
In this query, you can see the CXPACKET wait type and other information like the duration for the wait type. In the displayed scenario wait duration is 1713 milliseconds. The Blocking session id column gives you an understanding about which session is blocking the current session. In the CXPACKET scenario, no other external session is blocking the current session but the same session itself.
After analyzing both scenarios, it is clear that CXPACKET wait type occurs because of the parallelism. Next question is, how we can avoid the CXPACKET wait type. As said previously, sometimes parallelism is not something you can avoid. In a case of data warehouse fact table example, most of the time you need it to just execute and no intervention is required. If the CXPACKET is occurring because of a table scan, most likely that table doesn’t have a correct index or incorrect query plan. Either you can create an index or remove the bad cached query plan by executing sp_recompile for the stored procedure. If the index in place, most likely that index statistics are out of date. So you can start update statistics. If that won’t help, then it might be that index has fragmented and index rebuild is required.
After going through the DOs for CXPACKET, there is a DON’Ts for CXPACKET. You can run the query on one processor by using OPTIONS as shown in below query to eliminate CXPACKET:
|
1 2 3 4 5 |
SELECT * FROM SAMPLE WHERE NAME6 = 'A' OPTION ( MAXDOP 1) |
By doing this though the CXPACKET is removed and a query will be executed only in one processor. However, this will slow down the query as it will run on one processor.
Finally, CXPACKET is not a problem but it could be a symptom of a missing index or out of date statistics.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021