Is Parallel Processing Causing Problems?

Darren Williams 2013-08-06 10:39:43

I have a fairly simple query which using the default SQL server configuration takes about 3 hours to complete.

Looking the query plan I noticed that the query is being run in a parallel thread mode. A bit of googling lead me to believe that this was the cause of the query taking so long to run.

I then added

option (maxdop 1)

to the end of my query to force into single processor mode. The query now takes about 8 minutes to run.

I believe that the Server Manager needs to take some time out and look at the settings he's used to configure the server, including the thresholds for parallel processing to kick in.

Any pointers on recommended settings for parallelism would be grateful.

/storage/attachments/645-longquery_Anonymized.queryanalysis
Andy 2013-08-06 10:43:30
Hi Darren,
I've also seen this in the past where a simple query gets itself into a mess during parallelism. Turning MAXDOP to 1 for me also worked.
While I don't have any pointers directly, what we did do was a bit of trial and error on our test system to see where things started to get messy and found a happy medium.

In the end the decision was to set the server to MAXDOP 1 and left it at that. We've not had any trouble since.

Regards,
Andy

JasonRHall 2013-08-06 12:31:01
Hi guys,

There actually could be several reasons for something like this. If you could run the query in PE and attach the actuals in the form of a .queryanalysis or .pesession file we may be able to tell you more.

Beyond that I would also recommend a bit of caution with regard to using MAXDOP(1) especially at the instance level. If you get a chance, have a look at item #3 on this blog post.

Darren Williams 2013-08-06 13:19:10
Unfortunately, the machines we actually run queries on are locked down, so I couldn't install PE on a machine connected to the server. All I could do was install PE on a standalone machine and then load the plan I exported from management studio.
SQLkiwi 2013-08-13 14:12:49
Darren, SSMS can also collect an 'actual' query plan, which can then be exported to PE. Runtime information is extremely useful for plan diagnosis.
wayne 2013-08-15 13:57:50
The heaps are definitely bumping the IO cost enough for a parallel plan. the fact that you are joining table2 twice leads me to think that you could have some sort of hierarchy stucture. The right indexing will certainy give you a chance of moving from blocking and resource hungry hash joins to perhaps a merge join without pre-sorts. if this is hierarchical data take a look at nested sets.
August 6, 2013
No Comments