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!

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.