SQL Server Notes by AB | Note #22 | CXPACKET Wait Type - Part 1 | #ABSQLNotes
SQL Server Notes by AB?| Note #22 | CXPACKET Wait Type - Part 1 | #ABSQLNotes
CXPACKET stands for Class eXchange Packet. This is one of the wait types in SQL Server. It simply means that parallelism is happening inside SQL Server. Parallelism means that your query is using more than one thread. There is a server setting called "Cost Threshold of Parallelism" (CTP). If the cost of the query is more than then the value specified in CTP setting, SQL Server decides to use multiple threads to run your query. All these decisions are taken during the optimization phase of the query even before the query has started running. Let's say your server has 8 logical processors and SQL Server is configured to use all of them. Now when the query, which is marked to run in parallel, starts running, SQL Server execution engine will create 9 threads for this query, one for each logical processor. So why 9? The first thread, Thread0, will be the controller thread, and the remaining ones, Thread1 to Thread8 will be the workers that will do the job. Thread0 registers for the CXPACKET wait type. This is parallelism and CXPACKET wait type in action. Ok, so all good, but when does the CXPACKET wait type trigger? Well, in parallelism, the distribution of work is uneven among the threads. When one thread finishes its job earlier than the other, it waits on CXPACKET wait type, waiting for other threads to complete their work. There are other engine aspects too that register for the CXPACET wait type and now we are powered with another wait type called CXCONSUMER but all of this discussion is beyond the scope of this short note. CXPACKET wait time may or may not mean a performance problem - it is tricky business. A few other things need to be evaluated before you jump to a conclusion that the wait time of CXPACKET is very high and some optimization is required. Demo URL: https://www.youtube.com/watch?v=x6XPWn0DgPY. Want to read more SQL notes like this one? Here: https://bit.ly/ABSQLNotes.