Get Ready for SQL Server 2022

By:   |   Updated: 2022-03-28   |   Comments   |   Related: More > SQL Server 2022


Problem

In November 2021, Microsoft announced SQL Server 2022 to be released sometime this year. As with all previous versions of SQL Server, this version has many new features that will make the transition to SQL Server 2022 totally worth it. Whether you are planning on an upgrade or building a brand-new system in your data center, you should be aware of the new features of SQL Server 2022 and be ready to take advantage of these enhancements as soon as possible.

Solution

SQL Server 2022 has plenty of new features that make this an interesting release including its close ties to Azure. As more and more deployments are cloud based, it is interesting to see what enhancements are realized by using cloud technologies. As with other releases of SQL Server data platform, SQL Server 2022 can be deployed on-premises and in the cloud. Let’s take a look at some of these new features.

Performance Improvements in Microsoft SQL Server

As a DBA and someone that has been using SQL Server for over 20 years, it is always interesting to see the new tweaks and improvements the Microsoft team is able to introduce to the database engine. Keep in mind that all new features have to be intertwined with existing features, so it is amazing that after 20 plus years of SQL Server they are still able to make improvements that truly make SQL Server that much better.

Parameter Sensitive Plan Optimization

This is one subject where you will find many articles written about this topic, better known as parameter sniffing. With SQL Server 2022, the database engine can now support multiple cached plans for the same query with the only difference being the parameter values that are being used. This allows SQL Server to have the best plan for the workload it needs to solve versus having 1 plan to fit every need. The nice thing about this is that there is nothing you need to change in your code to take advantage of this enhancement.

TempDB Improvements

On very busy SQL Servers the use of TempDB to manage temporary objects can become a bottleneck and cause processes to be blocked. One way to solve this issue is by creating multiple data files for TempDB, but that doesn’t always solve the problem and there could still be TempDB contention. With SQL Server 2022, system page latch concurrency enhancements have been added to eliminate GAM (Global Allocation Map) contention and for concurrent SGAM (Shared Global Allocation Map) updates. This again is something where no code changes are needed to take advantage of this greatly needed improvement.

Query Store

The Query Store was introduced in SQL Server 2016 to help collect usage data for SQL Server objects and to help identify T-SQL performance bottlenecks that should be addressed. SQL Server 2022 continues to find new ways to use the Query Store and since it has been in use for several years without issue it will now be turned on by default in SQL Server 2022. In addition, you will be able to use the Query Store for read replicas for Availability Groups.

CE Feedback

Another new feature of SQL Server 2022 is the cardinality estimation feedback. With each new version of SQL Server, the database engine is tweaked to make improvements that should improve performance. But sometimes when migrating old databases to a later version of SQL Server we may end up having a degradation in performance. With this feature it can automatically detect and adjust the query plan to be used in the event that the workload regresses and degrades performance.

Azure Synapse Analytics

Reporting and analytics have always gone hand in hand with SQL Server. Microsoft has introduced several tools that allow business users better insight into the data. One of the issues with reporting and analytics is the overhead these types of queries can put on SQL Server and potentially cause a performance hit to your transaction database. With SQL Server 2022, there is the ability for near-real time data movement from SQL Server 2022 to Azure Synapse Analytics using Azure Synapse Link. This will allow you to keep your reporting dataset close to real time and offload these processes from your production databases. In addition, this allows you to take advantage of all of the Azure Synapse Analytics functionality.

Azure SQL Managed Instance for DR

This allows you to create a distributed availability group to an Azure SQL Managed Instance for disaster recovery. You can also use these as read-only replicas. So, with this feature you can take your on-premises database and setup DR using an Azure SQL Managed Instance to keep your data in sync in the event of a failure. To make this super simple, there is a wizard interface that you go through to pick some options and enter credentials, and behind the scenes all of the connectivity and data movement processes are automatically setup for you. In addition, can restore the Azure SQL Managed Instance databases back to your SQL Server. This restore process is something new that has not been available with previous versions of SQL Server.

Security

SQL Server 2022 also introduces several new security related enhancements such as the SQL Server ledger which brings blockchain technology to SQL Server, enhancements to always encrypted, secure encrypted connections, as well as additional granular permissions and roles.

Next Steps

These are just a few of the changes that are coming with SQL Server 2022. To learn more about SQL Server 2022 check out these resources:

MSSQLTips.com Newsflash sponsored by Microsoft.



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


Article Last Updated: 2022-03-28

Comments For This Article