CXPACKET means that the SQL Server is waiting to synchronize a parallel running query.
In short, then if you have a query that runs on 4 CPU's, then one of them is used to gather information from the others. Until the 3 other's are finished, then the 4th is waiting for CXPacket.
CXPacket can also occur when the 3 other are skewed, that means if one finish very fast and the 2 others take longer time, then the first one has to wait on the three others.
So when you run parallel queries, then CXPACKET will always be there, and it is only when it get's high that i can be a problem.
What to do
I would recommend to investigate parallel queries and see if they should run parallel or not. If not and it is few queries, then add the MAXDOP queryhint to each query.
If it is a general problem, then you can change the server settings:
- Max degree of parallelism
- Cost threshold for parallelism
they are default 0 for max degree of parallelism and 5 for cost threshold for parallelism.
Let's start with the last one. All plans are evaluated as if they should run sequentially. If the cost for the query reaches a certain point (5), then SQL server evaluates a parallel plan.
when a parallel plan will be evaluated, then max degree of parallelism determins how many CPU's that can be used (in short term). 0 = use as many as you want. 1=1 cpu, 4= four cpu's and so on.
You have to test what you want to do with these two, but there are some general guidelines:
- If you system has only small queries (single inserts and simple selects), then you can set max degree of parallelism to 1.
- If you have a system with very complex reporting/queries, then you can set parallelism to 0 or a number of CPU's (eg. 4 if you have 16 CPU's).
- If you do both, then you can set CPU's to a number greater than one, e.g. 4 if you have 16 CPU's and increase cost thresshold for parallelism to e.g. 25.
But you have to test. Sometimes I have seen customers with a very high CXPACKET, and then suddenly it is 0. That is because the customers has set MAX DEGREE OF PARALLELISM to 1. But that is very often a bad idea, because we ofcourse don't have problems with parallelism anymore, but performance will ofte degrade.