Things SQL Server vNext Should Address: Adaptive DOP

Posted on by Erik Darling

Speed Demon

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.

Riddled

Right now, SQL Server can downgrade DOP when a server is under CPU pressure. I have a demo of that in this video about sp_PressureDetector.

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.

Think big.

Ghost Whopper

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!

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

Related Posts