For years, when you right-clicked on a database and click Properties, the “Compatibility Level” dropdown was like that light switch in the hallway:
You would flip it back and forth, and you didn’t really understand what it was doing. Lights didn’t go on and off. So after flipping it back and forth a few times, you developed a personal philosophy on how to handle that light switch – either “always put it on the current version,” or maybe “leave it on whatever it is.”
Starting in SQL Server 2014, it matters.
When you flip the switch to “SQL Server 2014,” SQL Server uses a new Cardinality Estimator – a different way of estimating how many rows are going to come back from our query’s operations.
For example, when I run this query, I’ll get different estimates based on which compatibility level I choose:
WHERE DisplayName LIKE 'Jon Skeet'
AND Reputation = 1;
When I set my compatibility level at 110, the SQL Server 2012 one, I get an estimated 239 rows:
Whereas compatibility level 120, SQL Server 2014, guesses 1.5 rows:
In this case, SQL Server 2014’s estimate is way better – and this can have huge implications on more complex queries. The more accurate its estimates can be, the better query plans it can build – choosing seeks vs scans, which indexes to use, which tables to process first, how much memory to allocate, how many cores to use, you name it.
You read that, and you make a bad plan.
You read that the new Cardinality Estimator does a better job of estimating, so you put it to the test. You take your worst 10-20 queries, and you test them against the new CE. They go faster, and you think, “Awesome, we’ll go with the new compatibility level as soon as we go live!”
So you switch the compat level…and your server falls over.
It goes to 100% CPU usage, and people scream in the hallways, cursing your name. See, the problem is that you only tested the bad queries: you didn’t test your good queries to see if they would get worse.
Instead, here’s how to tackle an upgrade.
- Go live with the compat level you’re using today
- Wait out the blame game (because anytime you change anything in the infrastructure, people will blame your changes for something that was already broken)
- Wait for the complaints to stabilize, like a week or two or three
- On a weekend, when no one is looking, flip the database into the newest compat level
- If CPU goes straight to 100%, flip it back, and go about your business
- Otherwise, wait an hour, and then run sp_BlitzCache. Capture the plans for your most resource-intensive queries.
- Flip the compat level back to the previous one
On Monday morning, when you’re sober and ready, you compare those 10 resource-intensive plans to the plans they’re getting in production today, with the older compat level. You research the differences, understand whether they would kill you during peak loads, and start prepping for how you can make those queries go faster under the new CE.
You read Joe Sack’s white paper about the new CE, you watch Dave Ballantyne’s sessions about it, and you figure out what query or index changes will give you the most bang for the buck. Maybe you even resort to using hints in your queries to get the CE you want. You open support cases with Microsoft for instances where you believe the new CE is making a bad decision, and it’s worth the $500 to you to get a better query plan built into the optimizer itself.
You come to the realization that the old CE is working good enough for you as it is, and that your developers are overworked already, and you can just live with the old compatibility level today. After all, the old compatibility level is still in the SQL Server you’re using. Yes, at some point in the future, you’re going to have to move to a newer compatibility level, but here’s the great part: Microsoft is releasing fixes all the time, adding better query plans in each cumulative update.
For some shops, the new CE’s improvements to their worst queries are worth the performance tuning efforts to fix their formerly-bad queries. It’s totally up to you how you want to handle the tradeoff – but sometimes, you have to pay for the new CE in the form of performance tuning queries that used to be fast.