SQL Server 2019: Compatability Level’s Day Of Reckoning

Skin Tags

Stop me if you’ve heard this one:

“We recently upgraded to SQL Server 201(4, 6, 7), and performance is AWFUL…”

And the problem was pretty easily solved by flipping the compatibility level back to 110, which fixed (most) of the issues?

(Or just went back to having the issues they knew that they had before, which is often far less scary.)

In those versions, flipping compatibility level uses the new Cardinality Estimator (CE). That new Cardinality Estimator is real hit or miss.

The worst part is that there’s practically no gain to be realized for using higher compatibility levels — that changes with SQL Server 2019.

Feature Creature

There are two things that are pretty cool in SQL Server 2019: Scalar UDF Inlining (FROID), and Batch Mode for Row Store (BMFRS?).

FROID potentially solves a big problem that’s been plaguing SQL Server users for decades. Scalar UDFs are just straight up performance poison.

This fixes the problems with them (I mean, sure, not every UDF is eligible, and you can run into other problems, but still…).

BMFRS does a bunch of stuff: It makes Batch Mode processing available for Row Store indexes (duh), it also makes Adaptive Joins and Memory Grant Feedback available for them.

Those two things were introduced in 2017, but only available if you used column store (which is what Batch Mode was originally created for).

These things have the potential to fix some very big workload problems for people.

But there’s a thing.

Monkey Paw

In order to use them, you gotta be in compatibility level 150. That also brings along the new CE.

You could be trading one set of problems for another, here. That makes flipping the switch a hard sell.

It all depends on where your biggest problems are, and the time and resources you have to fix regressions.

For most people, it’s not realistic to test their entire workload. You can test your most important queries, as long as they’re reliable.

This is a good place to plug Workload Tools by Gianluca Sartori, which can make this easier.

You can also flip the switch during a low usage time and see if monitoring freaks out.

If it doesn’t, great. If it does, you have a lot of work to do.

Of course, if you’re on SQL Server Standard Edition, this might not matter. As of this writing, I have no idea if these two features will be available there.

Whack-A-Query

The addition of these two features is pretty neat. I’m excited for them.

I’m also very interested to see how customers react, both from the point of view of adopting SQL Server 2019, and adopting compatibility level 150.

I bet a lot of people are gonna want UDF inlining without having to buy the cow.

Thanks for reading!

8 thoughts on “SQL Server 2019: Compatability Level’s Day Of Reckoning”

  1. After seeing Brent’s session on 2019 at the Dallas event, I’d definitely love to have UDF inlining without some of the other changes. The potential changes around parameter sniffing have me a bit concerned as we have a multi-tenant system with both large and small clients.

  2. Could also flip to compatibility level 150 but ‘force’ Legacy CE via global TF 9481 or database scope configuration ‘Legacy cardinality estimation’.

    That’s my plan when needed.

  3. The first quote occurs because they MUST update stats with fullscan to resolve the issue. An alter index rebuild will get you partially there, but in other cases update the stats with fullscan to solve the rest of them. Did they in-place it, fork-lifted to another machine, or throw in some more RAM/SSDs? So many options. Well, E. Darling, you know the stats from 2012 are not compatible in 2016 or the latter. Why the need to scare people? Lets get back to lifting.

    1. Yeah, that’s great advice when your database is the size of AdventureWorks. Out in the real world, people have 100s of gigs or a few terabytes of data.

      You should definitely tell them to update all their stats with fullscan though.

      I’ll be over here 😂😂😂

      1. Oooh the reeeaal world 🌎🌋…I wonder when I’ll get to see it. Don’t you work at 🏡?What is this adventure wreck you speak of? Hugs

Leave a Reply

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