Intelligent Query Processing (IQP) is quite a neat set of features. It allows the SQL Server Engine some flexibility in the plans that get used and re-used.
One in-flight example of IQP is the Adaptive Join, where a choice between Hash and Nested Loops Joins can be made at runtime based on a row threshold.
I think that threshold should also apply to serial and parallel plans, too.
The query plan will still look like it’s going parallel, but in reality it will only be running on a single thread.
Here’s the thing: I think that should happen more often, and I think it should be based on the same row thresholds that are used for Adaptive Joins.
If a query starts running and
- It’s a serial plan, but way more rows start getting processed, DOP should scale up
- It’s a parallel plan, but way fewer rows get processed, DOP should scale down
Perhaps the first point could be addressed more aggressively than the second, because it’s far more likely to cause a performance issue, but hey.
Queries that process lots of rows are typically the ones that benefit from going parallel.
Eight threads dealing with a million rows a piece will go a lot better than one thread dealing with eight million rows on its own.
This is another important piece of the parameter sniffing puzzle, too. Often I’ll be able to tune queries and indexes so that the same general plan shape is used, but the key difference is a parallel plan still being much better for a large data set.
Right now, I’m a little stuck optimizing for the large value, or using dynamic SQL to get different query plans.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Things SQL Server vNext Should Address: How Did I Do?
- Things SQL Server vNext Should Address: Add Lock Pages In Memory To Setup Options
- Things SQL Server vNext Should Address: Add Cost Threshold For Parallelism To Setup Options
- Changes Coming To SQL Server’s STRING_SPLIT Function: Optional Ordinal Position