Downside to running a query in parallel

Question

Downside to running a query in parallel

water490

SSC Eights!

Points: 959
More actions
February 23, 2024 at 4:03 am

#4383300

I am a SS rookie. To me it seems like running a query in parallel whenever possible is a great idea b/c that way more work can be done in the same time. Are there any downsides to running a query in parallel?

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Yep. Lets say you are doing an update on a table or insert which requires an exclusive lock on the table. Having the query go parallel creates self-blocking as you need to wait for thread 1 to release the lock.

Another scenario you are pulling data from a table that is HUGE and the data is spread out across the disk (assuming HDD here, SSD wouldn't have that big of an issue). Thread 1 asks for data in sector 1 while thread 2 is asking for data in sector 1000000 and thread 3 wants data in sector 2 and so on. The read head on the disk is just thrashing across the drive to find the data you want.

Another scenario - you have 128 cores on the machine dedicated to SQL server and have MAXDOP set to 0 (so any query can use up all 128 cores). You have a stored procedure that is a read only (SELECT only without UPDATE, INSERT, or DELETE, but lots of calculations) called frequently that uses up all 128 cores and takes 2 seconds to complete. You have 100 users call that SP at the exact same time. One of those users needs to wait 200 seconds to get their data. User 1 will say the query is fast (2 seconds to get their data... not ideal, but still pretty quick). User 100 will say you need to tune things as they are not waiting almost 3 minutes to get their data.

Even if the query only uses 1/2 the cores, you still have user 100 waiting 100 seconds for the data. I have found that in a LOT of cases, more cores doesn't mean faster results. SOMETIMES (frequently) the bottleneck isn't the CPU and if the bottleneck is something else (disk, network, memory), more threads isn't going to make your query better and can make things worse.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

water490 wrote:

I am a SS rookie. To me it seems like running a query in parallel whenever possible is a great idea b/c that way more work can be done in the same time. Are there any downsides to running a query in parallel?

To summarize a bit of personal experience...

Parallelism doesn't produce linear advantages. If you measure a run with a MAXDOP of 4 and an otherwise identical run with a MAXDOP of 8, you will usually NOT get the job done in half the time as 4 when using 8 at the cost of substantially more CPU, as well.
It's been proven that really low cost threshold runs can be seriously hurt by going parallel (see #1 above). That's why there is a general recommendation that the default Cost Threshold of Parallelism of "5" is too low and that a much better "starting point" is "50".
I won't take the time to prove it here but, for the workloads we have at work, a MAXDOP of 4 works the best "overall" for us. We've set the server wide default to 4 and allow for queries known to do better at 8 to use an optional OPTION(MAXDOP 8).
I never leave the default Cost Threshold for Parallelism set to "5" nor the default server wide MAXDOP setting set to "0". YMMV but the defaults are generally horrible and I've not seen a case when they're good.
Parallelism is important... make like a query and read some pages...

https://www.red-gate.com/simple-talk/databases/sql-server/learn/understanding-and-using-parallelism-in-sql-server/

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#parallel-query-processing

Here are some articles on what can prevent parallelism. I've actually verified most of these as actually being true.

https://www.brentozar.com/?s=scalar+functions

https://www.brentozar.com/archive/2016/01/another-reason-why-scalar-functions-in-computed-columns-is-a-bad-idea/

https://www.brentozar.com/archive/2020/11/how-scalar-user-defined-functions-slow-down-queries/

https://www.brentozar.com/archive/2020/11/how-scalar-user-defined-functions-slow-down-queries/?utm_source=ssc&utm_medium=pubemail

https://www.brentozar.com/archive/2022/06/sql-server-2022-tells-you-why-a-query-cant-go-parallel/#comment-3570220

https://www.brentozar.com/archive/2020/10/using-triggers-to-replace-scalar-udfs-on-computed-columns/

https://www.brentozar.com/archive/2018/02/using-noexpand-get-parallelism-scalar-udfs-indexed-views/

--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)

water490 SSC Eights! Points: 959 More actions · Answer 3

thank you everyone. What recommended settings for parallelism should I set SS to? Clearly the defaults are not recommended.

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

water490 wrote:

thank you everyone. What recommended settings for parallelism should I set SS to? Clearly the defaults are not recommended.

If you read the text in my post, I tell you.

--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)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

To add to this, settings for parallelism are not some "magic" number that can be applied across any environment. If it could be recommended with little to no information, then Microsoft could bake it into the SQL engine to pick good values during install time. There are a lot of factors that come into play when setting up MAXDOP and your cost threshold for parallelism. There are some cases where MAXDOP of 0 may be the correct value or even a value of 1 could be correct. Cost threshold last I heard, the community best practice was to set it to 50 initially and adjust as needed. And even after you get it set up correctly, there may be cases where you have a query that goes parallel and shouldn't OR should go parallel and doesn't and you need to add query hints.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.