Sometimes the SQL server can benefit of having multiple processors to split a job between them, and other time it causes a very big problem.
A lot of guys have written some very useful blogs about that, but when I visit customers then I start to investigate what there waittypes are.
Please read this post, to read a little bit about how to identify waittypes.
If you see CXPackets, then you must start to be clever and investigate a lot, because there can be many solutions to the waittype CXPackets.
What I would do is, that I would try to set:
sp_configure ‘show advanced options’, 1
sp_configure ‘cost threshold for parallelism’, 25
sp_configure ‘max degree of parallelism’, 4
What this means is:
- First we turn on advanced options, so that we can work with the two other options.
- Then I set cost threshold for parallelism to 25. It could be 30, 20 e.g. I think that it is 5 per default. It means basically: “In terms of cost to run the query in parallel. How high should it be, before we consider parallelism. the higher, the fewer queries is run by using parallelism.
- Finally we set the max degree of parallelism to 4. It could be 2, 3, 8. 0 means unlimited and 1 means no parallelism. I would set it to half of the processors that I have available.
I know that you should do a lot more investigation, but often you have a lot of other work to do, and you might not be so good at performanceoptimization, so the pragmatic approach is to try and test.
Let me point out, that a high CXPackets is not always a problem, but if you e.g. look at your processors and see a high utilization together with cxpackets, then you might try the change in this blog.