Joey on SQL Server

Introducing SQL Server 2022: Top 3 New Features Announced at Microsoft Ignite

The next major version of Microsoft's flagship database engine is now in private preview. Here's what you need to know about it.

• By Joey D'Antoni
• 11/02/2021

Microsoft introduced SQL Server 2022 Tuesday morning during Executive Vice President Scott Guthrie's keynote at Ignite 2021. The product is only in private preview, so the full details of the release are not yet publicly available. However, Microsoft shared some exciting details of the new release during a session with Asad Khan, principal lead program manager.

As you can imagine, there are a lot of features that support Azure enablement. Beyond Azure, though, there are major improvements to the core performance features of the database engine. Here's a rundown of the most notable new known features. (Also, keep in mind that this is not the full list of feature enhancements, which will become available as SQL Server 2022 moves into public preview.)

1. Moving Into and Out of Managed Instance
Azure SQL Managed Instance is a platform-as-a-service (PaaS) offering that was built to simplify migrations from either on-premises or cloud-based virtual machines into PaaS. The service is much closer to the boxed SQL Server product than Azure SQL Database is. However, there were still challenges with this service.

Until recently, executing migrations with limited downtime required a complex implementation of database migration services (DMS). Recently, Microsoft announced the ability to use a simplified transaction log shipping mechanism, which reduces the amount of downtime needed for a migration to a few minutes. However, there was another hurdle: If you moved to Managed Instance and had to move back for cost or performance reasons, you could not move your databases back into SQL Server.

At Ignite, Microsoft addressed this problem in both fronts. SQL Server 2022 will support migrations to Managed Instance through the use of distributed availability groups, which will enable near-zero-downtime database migrations. Additionally, you will have the ability to move back to on-premises through a database restore (only available for SQL Server 2022). The distributed availability group framework typically allows for easy failback during a database or hardware upgrade, and I would expect the same behavior to be supported in this Managed Instance scenario.

2. Better and More Query Store
The Query Store feature that was introduced in SQL Server 2016 collects data about query execution plans and runtime history for a given query. This feature has been subtly improved in each release since its introduction, but it still has one glaring gap: Readable secondary databases in Always On availability groups did not have access to write to the Query Store. Not having this diagnostic data meant you had no way of tracking what query activity was on readable secondaries, both in SQL Server and Azure SQL Database.

SQL Server 2022 addresses this by providing write access to the Query Store from readable secondaries. This will provide transparency into the activity on those secondary replicas and provide better tuning options. Microsoft made no announcement about bringing Query Store Plan Hints to SQL Server 2022, but given the common path of Azure SQL features into SQL Server, I would not be surprised to see that feature make the final release.

Beyond the Query Store addition, there are some other pretty major enhancements to the database optimizer. SQL Server's Query Optimizer has been getting smarter over time and more focused on common areas of performance issues with SQL Server. One of the ways Microsoft has accomplished this is by increasing the ability of the same execution plan to have changes -- like in the memory grant feedback feature that was added in SQL Server 2017. If a given query didn't have enough (or required too much) memory for sort and join operations, the memory granted would be adjusted on subsequent executions.

SQL Server 2022 is going to introduce some new optimizations, including some around cardinality estimates (the number of predicted rows returned by an operator or query) and changes to the maximum degree of parallelism (MaxDOP) for a given query execution. Changing MaxDOP for differing parameter values is a major improvement. For example, a query with a WHERE clause that meant scanning millions of rows would benefit greatly from additional threads to process the scan, whereas a query that only returned a single row would benefit from a single thread retrieval operation, without the overhead of bringing parallel threads back together.

But that's not the biggest change Microsoft announced to SQL Server 2022's query execution.

Anyone who has worked with databases for more than a couple of weeks is familiar with the following behavior: A user reports executing a query that typically runs in seconds, but is now taking minutes or hours. The most common cause for this is a behavior called parameter sniffing. The first (or second) time you execute a query, SQL Server generates an execution plan, which is based on the parameter values you passed in that initial execution. This plan is cached in memory (generating execution plans consumes a lot of CPU cycles). For subsequent queries with the same hash value but vastly different parameters, that plan may be suboptimal. Microsoft refers to these plans as "parameter-sensitive plans," where a change in parameters can result in a significant difference in runtime. There is not a lot of public detail about the implementation of this feature yet, but if this is successful, it can provide big performance gains to a common pain area.

3. Security and Blockchain
One of the headaches faced by Azure SQL Managed Instance customers was that they had to switch their client applications from connecting with traditional Active Directory connections, which used Kerberos, to using Azure Active Directory authentication, which relied on more protocols like OAuth and SAML. This was easy if you owned your application and could update client libraries, but not for third-party applications with less control. With SQL Server 2022, Microsoft is introducing support for Kerberos authentication, which should make that transition a lot easier. This answers one of the more common questions I've gotten about Managed Instance in my sessions, so there is definitely a need.

Azure SQL Database has a distributed ledger service, which can provide additional levels of integrity to data in databases by preserving the full transactional history of a table stored in immutable distributed storage. To simplify this concept and save you a Google search or two, it's effectively an audit record that even the DBA cannot overwrite, and it's stored in a trusted storage location -- in this case, write once, read many Azure Blob Storage. SQL Server 2022 is going to have ledger tables. As we move further into the release cycle, it will be interesting to see the implementation details for on-premises and Azure VM deployments.

SQL Server 2022 looks to be a promising release. While there are a lot of enhancements to support Azure (as always), there are also significant improvements to the core database engine that will pay dividends no matter where you are running SQL Server.

Read my other column this week for more details on some of the Azure announcements made at Ignite. Also, look forward to more and deeper reporting on SQL Server 2022 when it becomes publicly available.