SQL Server 2022 New Features

Introduction

On November 2, 2021, Microsoft announced the private preview of SQL Server 2022, which is due for GA release “sometime in CY2022”. This public announcement at Microsoft Ignite breaks a long period of total silence about the next version of SQL Server. This post is about some of the most interesting SQL Server 2022 new features and improvements.

Due to keynote time constraints at Ignite and other marketing considerations, Microsoft only has announced a subset of new features and improvements in SQL Server 2022. More information will be released at the PASS Summit.

These are a few of the most interesting new performance-related features that we know about so far:

Parameter Sensitive Plan Optimization

With SQL Server 2019 and older, you will often have some stored procedures that are very sensitive to what input parameter values were used the first time the stored procedure was executed. SQL Server will use parameter sniffing to get the parameter value and come up with a “good enough” query plan based on that initial parameter value.

For example, with some parameter values that only return a few rows, SQL Server may use an index seek. With other parameter values that return many, many rows, SQL Server may use an index scan. Whichever parameter value gets used the first time the query is executed determines which type of query plan (seek or scan) that goes into the plan cache.

Parameter sniffing generally works very well, but it sometimes causes terrible performance regressions, for no easily apparent reason. Performance is good and then suddenly is terrible, because a “bad” query plan gets cached. There are many ways (including using plan freezing in Query Store) to try to avoid this common problem.

Bob Ward demonstrates Parameter Sensitive Plan Optimization starting at 9:04 in the video below.

What’s New in SQL Server 2022

Parameter Sensitive Plan Optimization will purposely cache multiple parameter sensitive active query plans per stored procedure or parameterized query. This has the potential to mitigate many common problems with parameter sensitive query plans without any code changes and without using Query Store.

All you have to do to get this functionality is to change your database compability level to 160. This may be one of the biggest improvements in the entire SQL Server 2022 release!

Query Store Improvements

In SQL Server 2022, Query Store will be enabled by default on new databases. This shows how confident Microsoft is in the benefits of Query Store. If you restore a database from an older version of SQL Server, you will still have to enable Query Store yourself. This is the same behavoir that we have seen since SQL Server 2016.

There are also additional Intelligent Query Processing (IQP) enhancements in Query Store. These include MAXDOP and CE model feedback using Query Store to create a feedback cycle to automatically adapt and resolve problems with common query patterns related to MAXDOP and CE models. There are also new enhancements to memory grant feedback including persistence and new algorithms to smoothly handle memory grant fluctuations. This will build on the IQP improvents in SQL Server 2017 and 2019.

Query Store will now be supported on AG read-only replicas allowing performance analytics on your read-only workloads. You will also be able to use Query Store hints to more easily shape query plans with no code changes.

Final Words

SQL Server 2022 looks like a big release, even based on the limited public information that we have so far. Here are some more links about SQL Server 2022.

In the meantime, if you are on SQL Server 2019, you should make an effort to get on SQL Server 2019 CU13 as soon as you can test and deploy it.

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!

SQL Server 2022 New Features

Introduction