Max degree of parallelism - Query costs

Question

Max degree of parallelism - Query costs

SQLSACT

SSC-Insane

Points: 21838
More actions
February 27, 2018 at 5:52 am

#403142

Hi All
I've analyzed my plan cache and isolated a query with a cost of 19.1
This query uses parallelism
My system is configured with a max degree of parallelism of 0. (Server has a total of 24 CPU's)
I changed the max degree of parallelism on the instance from 0 to 4.
I immediately noticed that the cost of the same query went up to 29.8
Just for my own understanding, why would the cost of the query increase when changing the max degree of parallelism?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SQLSACT - Tuesday, February 27, 2018 5:52 AM
Hi All
I've analyzed my plan cache and isolated a query with a cost of 19.1
This query uses parallelism
My system is configured with a max degree of parallelism of 0. (Server has a total of 24 CPU's)
I changed the max degree of parallelism on the instance from 0 to 4.
I immediately noticed that the cost of the same query went up to 29.8
Just for my own understanding, why would the cost of the query increase when changing the max degree of parallelism?
Thanks

The plan for the query will have changed from 24 threads to 4.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Thank you

The plan for the query will have changed from 24 threads to 4.

And this would affect the cost of the query?

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SQLSACT - Tuesday, February 27, 2018 6:21 AM
Thank you
The plan for the query will have changed from 24 threads to 4.
And this would affect the cost of the query?
Thanks

It does, yes. Guessing that the server you are tinkering with is a test server: change MAXDOP back to 0 (unrestricted), then copy out your parallel query twice to the same SSMS pane. Put OPTION (MAXDOP 24) after one of them and OPTION (MAXDOP 4) after the other, then compare the estimated plans.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

Although the COST of the query is a decent indicator during troubleshooting, it is just an estimate. The only way to tell if there's really been a change for better or worse is to execute the query before and after and test for things like CPU, logical reads, and duration. Never rely on cost estimates to make the final determination as to which scenario is better because you will be wrong more frequently than you would expect. I've seen cost estimates where one query says it will be 100% of the batch and the other query is 0% and when you actually do the runs, the performance is completely the reverse of that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

SQLSACT

SSC-Insane

Points: 21838

March 1, 2018 at 3:50 am

#1981606

Thanks all for the assistance. 🙂