SQL Server 2019: Is Bigger Better?

Outline

In SQL Server 2019, a few cool performance features under the intelligent query processing umbrella depend on cardinality estimation.

  • Batch Mode For Row Store (which triggers the next two things)
  • Adaptive Joins
  • Memory Grant Feedback

If SQL Server doesn’t estimate > 130k(ish) rows are gonna hop on through your query, you don’t get the Batch Mode processing that allows for Adaptive Joins and Memory Grant feedback. If you were planning on those things helping with parameter sniffing, you now have something else to contend with.

Heft

Sometimes you might get a plan with all that stuff in it. Sometimes you might not.

The difference between a big plan and little plan just got even more confusing.

Let’s say you have a stored procedure that looks like this:

There’s quite a bit of skew between post types!

Working my way down

Which means different parameters will get different plans, depending on which one comes first.

 

Fourry Friends

When we run four first, this is our plan:

Disco Dancer

It’s not “bad”. It finishes in 116 ms.

But when we run 1 next, it’s fewer well.

Less good?

You decide.

Inching along

At 12 seconds, one might accuse our query of sub-par performance.

One and Lonely

When one runs first, the plan is insanely different.

22 2s

It’s about 10 seconds faster. And the four plan?

Not too shabby.

Four play

We notice the difference between 116ms and 957ms in SSMS.

Are application end users aware of ~800ms? Sometimes I wonder.

Alma Matters

The adaptive join plan with batch mode operators isĀ likely a better plan for a wider range of value than the small plan.

Batch mode is generally more efficient with larger row counts, the adaptive join means no one who doesn’t belong in nested loops hell doesn’t get stuck there (probably), and SQL Server will take a look at the query in between runs to try to find a happy memory grant medium (this doesn’t always work splendidly, but I like the effort).

Getting to the point, if you’re going to SQL Server 2019, and you want to get all these new goodies to help you avoid parameter sniffing, you’re gonna have to start getting used to those OPTIMIZE FOR hints, and using a value that results in getting the adaptive plan.

This has all the same pitfalls of shooting for the big plan in older versions, but with way more potential payoff.

I wish there was a query hint that pushed the optimizer towards picking this sort of plan, so we don’t have to rely on potentially changing values to optimize for.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *