About 10 years ago, Microsoft made changes to the Cardinality Estimator (CE) which caused some problems for SQL Server upgrades. When folks upgraded to SQL Server 2014, they also casually switched their databases’ compatibility level to the latest version, because for years that hadn’t really affected query plans. They just figured they wanted the “latest and greatest” compat level, without regard to the effects. That backfired badly when they suddenly got 2014’s Cardinality Estimation changes.

So for several years, whenever someone upgraded from older versions, and they complained about performance, the stock community answer was, “Change your compatibility level back to what it used to be.” In many cases, that just solved the problems outright, leading to blog posts like this and this.

Even today on SQL Server 2019 & 2022, this advice is still relevant! If you mess around with compatibility levels, you can absolutely change cardinality estimations in ways you didn’t expect.

One amusing example is SQL Server 2022’s cardinality estimation feedback. Even if your workload has been on 2014+’s “new” Cardinality Estimator for a while, 2022’s CE feedback can change cardinality estimations back to older versions! That’s a complex example, though, and I’d rather stick to simple examples for here on the blog.

Simple Example Where 2022’s CE Improved

Let’s say our code uses a scalar function that just implements an RTRIM:

Starting with SQL Server 2019, SQL Server can attempt to inline that scalar function. Not only does that affect the shape of the execution plan, but it also affects the estimates.

Let’s see it in action with the largest Stack Overflow database. We’ll start by building an index to support our query, and then we’ll run the same query in two different compatibility levels – first 2016, then 2022:

The actual query plans:

First off, the good news is that 2022 absolutely smokes 2016 simply because it inlines the function. The query can complete in about 1 second, as opposed to 2016’s 1 minute 16 seconds. But set that aside for a second and let’s look at the cardinality estimation changes.

The top plan (2016) does a clustered index scan because SQL Server 2016 made a hard-coded assumption that 10% of the rows would match our filter. The first operator (top right) estimated that 22,484,200 rows would come out of the clustered index scan, and then the second operator (the filter) estimated that 2,248,420 rows would match.

Gotta love those hard-coded estimates at exactly 10% because they’re easy to spot, but I do wish they had a yellow bang on ’em so that SQL Server would warn us that it’s making up estimates out of thin air.

The bottom plan (2022) uses the index AND it gets absolutely beautiful, bang-on cardinality estimates. The first operator (top right, index scan) estimated 18,882 rows would come out.

The haters will say, “Brent, that’s not fair, that’s a different feature and it’s not the Cardinality Estimator.” They’re wrong, of course: the important part is that things have been changing all around the Cardinality Estimator, changing its inputs, and so as a result it’s common to see different estimations coming out of the CE with each subsequent version.

Simple Example Where 2022’s CE Worsened

In the first example, our simple function used the exact same NVARCHAR(40) that our table’s DisplayName column uses. However, what happens if we change the function to use a more generic datatype, like NVARCHAR(MAX)?

We’ll run our queries again, and look at their actual execution plans:

Now, SQL Server 2022 ignores the index. Why would you ignore the index in a query like this? Because you estimate that too many rows will come back, making the key lookup too expensive. But… look more closely at 2022’s execution plan. How many rows did it actually think were going to come out after the function’s filter?

Just one. <sigh>

I have no idea what the sam hell bug this is, or which part of the engine it’s in – whether it’s the CE or scalar function inlining or the query optimizer. All I can say is that the estimate is hot garbage. As a result, the sort spills to disk because SQL Server didn’t allocate enough memory to sort the rows that actually came back.

So you can’t even say, “Thanks to 2019, scalar functions get inlined, so estimates are more accurate.” They’re not. They’re all over the place, even in simple cases like this where we’re just changing the length of a datatype. (Again: the example here in the blog post is purely to talk about cardinality estimation, but at least 2022’s query runs way faster in this case because the function gets inlined.)

The Moral of the Story

The moral of the story: keep your grubby fingers off the compatibility level switch until you’ve followed my migration instructions.

To learn more about topics like this, attend my PASS Summit pre-conference workshop in Seattle, Tuning T-SQL for SQL Server 2019 and 2022.