You may have noticed large variations in elapsed time for parallel queries while performing query tuning or observing a production workload. This post reviews some of the possible explanations for those variations. I’m testing on SQL Server 2022 RC0 but …
Category: Parallelism
What’s The Point Of DOP Feedback In SQL Server 2022?
Loud Engine
If you’re ever on a long flight and want something to fall asleep to, ask a DBA how to set MAXDOP.
Sometimes I even ask myself that question when I’m tossing and turning at night.
There are a …
SQL Server 2022 Finally Adds Actual Non Parallel Plan Reasons To Query Plans
Wayback Machine
SQL Server query plans have had, forever, pretty generic reasons embedded in the XML for why your query was prevented from going parallel.
Not for things like it not breaking the Cost Threshold For Parallelism barrier, which should …
Common SQL Server Consulting Advice: Setting MAXDOP And Cost Threshold For Parallelism
Dirty Work
There are three classes of administrator when it comes to parallelism settings
- Never changed the defaults
- Changed the defaults to something wacky
- Changed the defaults to something safe
The beginning of this post is for the first couple …
Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables
Do That, But Faster
Serial zones in parallel plans can leave a lot of performance on the table. One of the best ways to see that is with insert queries that do a lot of work. A big contrast …
An Undocumented Restriction For Parallel Inserts In SQL Server That Can Kill Performance
Insert Card
I’d like to start this post off by thanking my co-blogger Joe Obbish for being lazy and not blogging about this when he first ran into it three years ago.
Now that we’re through with pleasantries, let’s talk …
Wait Stats During Exchange Spills In SQL Server Parallel Query Plans
Inorganic
Lots of people say these are rare — and perhaps they are becoming more so — but I still see them with some frequency. That’s not a terribly strong indictment of SQL Server, but that’s not the point of …
How Many Threads Can One Query Use In SQL Server?
Overlook
Something that many people overlook about parallel plans is that while they are limited to DOP active CPUs, they can suck up way more threads.
How many more depends on how many branches can be active at the same …
Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server
Punching
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m …
Table Partitioning And Parallelism In SQL Server Standard Edition
Jammed, Not Jellied
UPDATE 2021-04-14: Microsoft has updated the documentation for all 2016+ versions of SQL Server to indicate that parallelism is available for partitioned tables in non-Enterprise versions.
There’s been a note in the documentation since SQL Server 2016 …