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
reconfigure
go

sp_configure ‘cost threshold for parallelism’, 25
reconfigure
go

sp_configure ‘max degree of parallelism’, 4
reconfigure
go

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s