What I’m looking forward to in SQL Server 2022

It’s been a a several weeks since Microsoft started releasing details on SQL Server 2022. Here are two of the new features I’m most interested in.

Parameter Sensitive Plan Optimization

Parameter Sniffing has been plaguing SQL Server for years, and it sounds like Microsoft is finally putting some real effort into fixing the problem. Traditionally, when a query is executed, SQL Server will build and cache one plan. It does this based on the first set of parameters used during the initial execution. All subsequent executions of the same query, regardless of the parameters, will use the exact same execution plan. This is not always ideal, as one set of parameters may perform better with an index seek, while another may perform better with an index scan. Starting in SQL Server 2022, Microsoft is planning to solve this problem with Parameter Sensitive Plan Optimization. From what they’ve demonstrated, this would work by having SQL Server cash multiple plans for the same query, and decide which one would be more appropriate for a given set of parameters. There are not many details available yet, and several questions are yet to be answered, such as how many plans might get cached per query. But in general, I’m still excited to see how this performs in the real world.

Bi-directional Failover between SQL Server 2022 and Azure SQL Managed Instance

This is another intriguing feature. For years, I’ve been cautious about moving too aggressively into Azure SQL. Aside from operational cost over time, my reasoning was largely related to the fact that once you had your data there, it was much more difficult to get it back out. This is because Azure SQL is always running the newest release of SQL Server, and downgrading to an on-premise SQL Server version was not fully supported. There were ways around it, but this may be the most streamlined way, thus far. This will also be a great news for those looking to run a SQL Server on-premise, but are not able to manage a full DR region. With SQL Server 2022, you’ll now be able to add an Azure SQL Managed Instance as a failover target. Once you enable this, Microsoft will setup up a Distributed Availability Group behind the scenes between your on-premise instance and your Azure SQL Managed Instance. After setup is complete, you’ll then be able to freely fail back and forth between Azure and on-premise.

What about you, what feature are you most looking forward to in SQL Server 2022?