Joey on SQL Server

Microsoft Build: A Deeper Dive into SQL Server 2022

Build 2022 marks the long-awaited public preview of Microsoft's next-generation database engine. Here are the top seven new SQL Server 2022 features to look out for.

• By Joey D'Antoni
• 05/24/2022

When Microsoft introduced SQL Server 2022 last fall at Ignite, it was only in private preview, which meant that anyone who had access was bound by a nondisclosure agreement. As of Tuesday at the Microsoft Build kickoff, however, the public preview is now available.

Now that more people have access to the SQL Server 2022 trial, I want to take a deeper dive into some of the new features of the database engine. Some of these will be the flashy, big marketing features, while others are deeper engine or T-SQL features that you can use in your day-to-day work.

First, a couple of quick logistical notes. You can download the public preview build starting Tuesday, May 24. However, you won't be able to use it for production purposes until it becomes generally available (GA), or through Microsoft's preview program. Expect to see SQL Server 2022 hit GA this fall (at any rate, Microsoft has committed to releasing it by the end of 2022).

Let's dive into the features.

1. AWS S3 Support
SQL Server has supported backing up to Azure Blob Storage since SQL Server 2012. This is great if you are using Azure; it's what I recommend to all of my customers.

Now, Amazon Web Services (AWS) customers -- or even on-premises products from a number of storage vendors -- can take advantage of the benefits of using object-based storage for their backups. You will also have the ability connect to data using AWS Simple Storage Service (S3)-compatible storage for data-ingestion purposes.

2. Query Store on Secondary Replicas
This feature will ultimately address the business-critical tiers of Azure SQL Database and Managed Instance (and SQL Server itself). One of the major benefits of Always On availability groups is the ability to scale-out read workloads. However, prior to SQL Server 2022, you lost the benefits of the Query Store in those workloads.

While at least SQL Server supported connections from third-party monitoring tools, in Azure SQL Database (or Managed Instance), the business-critical service tier has long supported a readable secondary. However, there is virtually no way to monitor this. Bringing the Query Store to secondaries will give DBAs visibility into those workloads, as well.

One final Query Store note: Remember the Query Store hint feature? It's part of SQL Server 2022.

3. Azure Active Directory Authentication
This is a forward-looking statement by me, but I think this feature is but one of many you will see in coming years, as Azure Active Directory starts to take prominence over traditional Windows Active Directory. I don't think this is going to happen overnight, but I think over time you will see more "traditional services" supporting Azure AD.

Anyway, this feature does require Azure Arc for SQL Server, which acts as an agent. You will have all the benefits of Azure AD authentication, the biggest of which is multifactor authentication (MFA). You should note that this is only for user authentication, not service accounts. As of the SQL Server 2022 public preview, this feature does not work in Azure virtual machines, but you can test from on-premises.

4. Buffer Pool Parallel Scan
This quickly went into the database internals. As database servers grow larger and larger, operations that scan the buffer pool (SQL Server's primary data cache) can become quite slow, especially machines that have 1TB or more of RAM.

SQL Server 2022 enables this operation to use multiple CPU cores running in parallel. This feature will simply be enabled by default. This will help a number of operations -- including availability group failover and restore operations, among others -- to be faster.

5. Optimized Plan Forcing
Since SQL Server 2016, you have had the ability to force a "known good" execution plan for a given query. Plan forcing can be used after a version upgrade to lock into a good plan, or to quickly address a performance regression. In fact, the automatic tuning feature (introduced in SQL Server 2017) relies on forcing plans through the Query Store framework. Optimized plan forcing reduces the time to compile a query and generate a plan based on storing a compilation replay script. I will be testing this feature more in coming months and will share more details.

6. Parameter-Sensitive Plan Optimization (PSP)
Microsoft has been adding a lot of features to the intelligent query processing family of features in recent versions of SQL Server. This feature addresses a long-known problem in database performance: stored procedures that have widely varying execution plans based on a parameter in the WHERE clause. Let's use a very trivial example:

DECLARE @i  INT

  SELECT  CustomerName, TotalSpend 

  FROM Customers

  WHERE CustomerID=@i

For a moment, let's ignore good database design (as many software vendors do) and pretend that we have 10 million entries for CustomerID No. 1, and another 10 million entries for the unique numbers 2 through 10,000,001.

This is an extreme example of what is known as a nonuniform data distribution. The database engine will generate a vastly different execution plan depending on whether the query is first compiled with a value of 1, or any of the other numbers. Since the database engine caches execution plans, the value at first compilation may be reused for less optimal values.

With PSP, the engine can store up to three different plans and choose one based on the value of the parameter at execution time.

7. DOP Feedback
One of the most common questions around setup is, "What should you set the Maximum Degree of Parallelism (MaxDOP) to?" It's typically half the number of cores on the server, but it depends on a lot of factors.

This feature allows the database engine to decide, based on the runtime history of a given query, what the best degree of parallelism is. This decision factor will be based on elapsed time and the system resources the query is waiting on, which can fluctuate

There are several other engine and T-SQL enhancements that I will cover in coming weeks and months. A couple of highlights to me include better JSON functionality, the ability to use time series functions and other deeper internal things that will improve performance. Stay tuned!