Problems with Parallelism

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:

  1. First we turn on advanced options, so that we can work with the two other options.
  2. 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.
  3. 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.



0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *