question

samkweber99 avatar image
samkweber99 asked

why does maxdop = 0 cause query to fail app?

We have a SQL Server 2008, Standard Edition test system set up for a major State contract. The application is running a select query which appears to hang the application when the MAXDOP setting = 0, but works ok when set to 1. Does anyone know why allowing more CPU's to work on a query would cause such a lock out? The system is in a VM so CPU's might be shared. Could that cause such an issue? We don't want to set the MAXDOP to 1 because we would like it to use some parallelism.
maxdop
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
It is parallelism. That's the only thing that makes sense based on the fact that you're taking away the ability to do parallel queries by setting the MAXDOP to 1. You've got all the information you need from the two answers below. Capture the execution plan and change your cost threshold.
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Not quite. It does indicate 5 seconds, but 5 seconds to run the query on a very particular architecture, which is now so out-of-date that even the broom cupboard it was hiding in is obsolete. Conor Cunningham sometimes shows a picture of the machine in question in his presentations...
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
What @thomasrushton says. But now, best to just think of the costs, which are from the execution plan by the way, as "cost units" and not any actual measurement known to man.
1 Like 1 ·
samkweber99 avatar image samkweber99 commented ·
thanks, unlikely a parallel operation because the operation is just a select that normally runs in a little over 5 seconds. The Cost Threshold for parallelism parameter is a possible solution, we will try changing it to 10 seconds instead of 5 and see if that eliminates the use of parallelism. It just seemed odd to me that the use of parallelism itself can cause a fatal action. Have you seen anything like this before from an application?
0 Likes 0 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
FYI: a cost treshold of 5 does not mean the treshold is 5 seconds. It's a number which indicates a complexity.
0 Likes 0 ·
Show more comments
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Did you take a look at the execution plan? Also run the query and have a look at your sessions (I suggest to install/run sp_whoisactive), you might block yourself because of a parallel operation.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
In addition to what @Wilfred van Dijk has said, it might also be worth looking at your "Cost threshold for parallelism" setting. See this article by [Jeremiah Peshka on BrentOzar.com][1] for a very quick overview. [1]: http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.