I decided to write about this issue partly as an execise in creating a WordPress blog item. I was doing some random reading and came across an article discussing SQL server default settings. The particular setting that caught my interest was Cost Threshold for Parallelism.
The default is 5, that is, 5 seconds. Here is what the article I referred to has to say about 5:
This is the Query Cost where SQL Server Engine will start using parallel plans for your query. It is the estimated elapsed time in seconds for your query to run if it’ll run serially or use only one CPU. Five seconds is pretty low and only applicable to a totally OLTP application – which is never the case. This will likely cause even simple queries to run in parallel, and you are bound to have even more complex queries in your application. Add that to the fact that you likely have multiple concurrent transactions. You’d rather have that simple query running serial than have it use all of your CPUs causing more transactions to wait for CPU resources.
And this is how you end up with the CXPACKET wait events.