By Kendra Little on December 18, 2023
According to Microsoft’s documentation, “Database compatibility level … allow[s] the SQL Server Database Engine to be upgraded while keeping the same functional status for connecting applications by maintaining the same pre-upgrade database compatibility level.”
But these days, the “functional status” of a database at a given compatibility level differs depending on whether you’re using SQL Server, Azure SQL Managed Instance, or Azure SQL Database – and in the hosted versions it may change anytime without notice. Surprise, your database behaves differently now!
The whole concept is breaking down, and this is bad news for users of both managed services and the boxed product.
An example: database compatibility level 150 (SQL Server 2019)
In November 2019, database compatibility level 150 became the default for new databases created in Azure SQL Database.
Compat level 150 introduced intelligent query processing features. However, which query processing features were enabled if you use that compat level changed over time.
The scalar UDF inlining feature was not available in Azure SQL Database or Azure SQL Managed Instance until September 2021, nearly two full years later.
At that point, if you were already at compat level 150, scalar UDF automatic inlining just … started… happening. Maybe you saw the blog post, maybe you didn’t. That “functional status” of the database moved a lot right beneath your feet.
That feature is basically magic, but magic is dangerous. Some queries get faster, some queries get slower.
This has become obvious with database compat level 160 (SQL Server 2022)
I wrote in a recent post that SQL Server 2022 Intelligent Query Processing features are hugely incomplete in Azure SQL Managed Instance. (I think this is true for Azure SQL Database as well, but to be honest it’s a lot to keep straight, so let’s use MI as the example.)
None of the following intelligent query processing features are available in Managed Instance:
- Cardinality Estimation Feedback
- Memory Grant Feedback (percentile)
- Memory Grant, Cardinality Estimation, and DOP feedback persistence
- Optimized plan forcing
- Parameter Sensitivity Plan Optimization
But let’s say you want to enable database compatibility level 160 so that you can use something like the new generate_series function or using enable_ordinal in string_split.
When will all those performance-changing features listed above magically become enabled?
How will you know?
If they cause a regression, how will you be able to tell what changed, and how long will it take you?
Dear reader, I am not excited to find out for myself, either.
This leaves “Boxed Product” SQL Server users feeling experimented on
The biggest group of users for SQL Server are still those who buy licenses and run the “boxed product” version, whether they are doing so on-prem or in VMs in the cloud. (I don’t say this from any official data, but from chatting with users and doing things like polling a room of ~100 students at a performance tuning day long session at conferences. There is some adoption of managed services for SQL Server, but it appears to be pretty limited, still.)
These users tell me they wonder: why aren’t these features available in the Managed Services?
- Will they cause instability in Query Store, which then causes it to not work?
- Do they not fully work as designed?
- Do they cause a lot of regressions if you enable them?
There is no way to know, because Microsoft hasn’t documented why these features aren’t available in Azure SQL Database or Managed Instance. Among users, I hear a lot of suspicions that if these features were effective and reliable, surely they would be made available to the people who Microsoft is working very hard to sell lucrative Managed Services to.
“There are database level controls,” some might say. That makes it worse for Managed Instance users, not better.
There are database scoped configuration options for many of these features. Maybe all of them, it’s kind of hard to read the list in the document with everything being in all caps and the names using abbreviations/ different wordings.
It is theoretically possible for a Managed Instance user who doesn’t want features to suddenly appear with minimal warning to:
- Diligently go through the documentation and manually disable all the settings for features which are not yet available for the service they are using in each and every database.
- Write automation to do this in new databases/instances.
- Manually review blogs and documentations every… I don’t know how often… to see if the features are available and then plan to test them.
There may be one user in the world who does that. Maybe two or three. It’s a bad idea, though.
This style of configuration is way too complicated. You’d have to document the reasoning all yourself and keep it up to date. It’s fragile: how likely is the upkeep of this configuration management likely to last if the person leading it changes teams? And it’s not even recommended by Microsoft: all the documentation says to rely on database compatibility levels. Going in this direction will only lead to weirdly configured instances that are hard to troubleshoot in the long term.
This approach is not why people want to adopt a hosted service for databases. It’s pretty much the opposite. And these are the kinds of problems that database compatibility level is supposed to make easier for users.
Make database compatibility levels make sense again
Dear Microsoft Senior leadership, there are two ways to handle this. Don’t let a database compatibility level mean totally different things in products which are supposed to be related, and which you can even fail over to one another.
- When you make a database compatibility level available in the SQL Server engine, ensure you make all the same features for that compat level available wherever it works as a standard practice.
Or…
- You can ditch the concept of database compatibility levels altogether and stop promising that Azure SQL Managed Instance is always up to date with the latest SQL features and functionality.
But you need to pick one.