In the era of technology defined by cloud computing, features evolve at the blinding speed of continuous deployment. When large software development organizations like Microsoft deliver semi-annual releases of products, like SQL Server, the volume of new features can be so large they can be hard to grasp. Microsoft continues to push the boundaries on what’s possible, both in on-premises and cloud data platforms, and they aim to make the life of data professionals much more manageable. The forthcoming release of Microsoft SQL Server 2022 is no exception. This release of the Microsoft flagship database engine will bring about new features to expand the capabilities of data professionals while increasing its scalability.
Recalling the earlier days of SQL Server, I used to dread the idea of performing a migration to a new version. Regardless of cool and exciting new features, the pains migrations caused were sometimes cringeworthy. Now, as I look at more recent releases of SQL Server, including SQL Server 2022, the migration path is loaded with new ways and means to get your ecosystem updated and running on the latest releases with ease. Use these new features to convince management to migrate. I assure you it’ll be worth it in the long run.
Performance Enhancements
Microsoft introduced Intelligent Query Processing (IQP) in SQL Server 2019, building upon their knowledge from observing workloads in Azure SQL Database. IQP has continued to evolve while providing easily implemented solutions to common performance-related issues while requiring nearly zero code change to the application. Common performance issues, including suboptimal T-SQL constructs like user-defined scalar value functions and multi-statement table-valued parameters, are among a few benefits of this new version of SQL Server, benefitting both DBAs and developers. SQL Server 2022 continues to build upon this foundation by placing some new tricks up its sleeve.
Maximum Degree of Parallelism Feedback
One of the enhancements coming from IQP was memory grant feedback. Memory grant feedback allows the query optimizer to become “self-learning” by automatically adjusting allocated memory for a given query, whether there’s too much or too little memory initially granted. SQL Server 2022 extends this self-learning to include the maximum degree of parallelism (MaxDOP) now, which is the number of threads used to execute a given query. This feedback allows the query optimizer to dynamically adjust the MaxDOP for a given query to further ensure optimal performance.
Parameter Sensitive Plan Optimization (PSP)
Parameter sniffing is a database behavior aiming to reduce CPU load by decreasing compilations for repeatedly executed queries. While this behavior is generally good, skewed data can lead to a common performance hindrance, which can sometimes be a nuisance to resolve. This occurs when the execution plan of a parameterized query isn’t efficient for a given parameter value. With parameter sensitive plan (PSP) in SQL Server 2022, the query optimizer can now cache up to three different versions of the execution plan to facilitate a wider range of possible parameter values. This ensures more consistent overall performance with little to zero code changes.
Query Store
Dubbed the “flight data recorder” of query performance, query store is a pivotal component of a data professional’s arsenal of tools. Not to mention, it’s the main data store for the intelligent query processing group of features. Originally released with SQL Server 2016, query store identifies performance issues quickly and easily for individual queries, allowing DBAs to force execution plans, identify regressions, or identify elevated waits statistics. However, one of the caveats with query store is if a readable secondary of the database was deployed, query store would only reflect metrics for a workload on the primary database.
Now, with SQL Server 2022, we can utilize query store on Always On availability groups read replicas to help improve the performance of those specific workloads. This new capability will provide the means for DBAs to performance tune with better precision read-only workloads.
Additionally, query store will now be enabled by default, ensuring new databases can take advantage of all the features dependent on query store to its full extent. You should note while this setting applies to newly created databases, it doesn’t apply to databases restored into SQL Server 2022, where you will have to configure this setting manually.
Expanded High Availability
Azure SQL Managed Instance Link
Planning for disaster recovery (DR) when utilizing VMs can become cumbersome and difficult to manage. SQL Server 2022 helps to reduce this complexity by bringing forth the ability to seamlessly provide a replication link directly to an Azure SQL Managed Instance. Since Managed Instance is a platform as a service (PaaS), the ongoing management of the underlying infrastructure is managed by Microsoft. Once the initial setup of the Managed Instance is completed, Microsoft takes care of the rest, allowing staff members to focus on other priorities.
Employing the distributed availability groups feature behind the scenes, this new link capability offers up bi-directional real-time replication of your most critical databases while continuing to offer enterprise-grade performance. Not only does this bring about an additional method for providing disaster recovery to your on-premises or in-the-cloud VMs, but it also facilitates a new migration method. Once the link feature is configured successfully, this functionality allows failover to the Azure Managed Instance replicas, thereby performing a seamless migration with minimal effort. This will help organizations get started with the PaaS platform with minimal risk of failing back to their original environment.
Improved Security
Data security continues to become more complex as attackers use new and creative ways to cause havoc. The release of Azure SQL Ledger provided a new way to ensure data integrity against bad actors and was initially released in Azure SQL Database. Ledger applies blockchain in conjunction with immutable blob storage and your Azure SQL Database. This combination of technologies allows a systems administrator to “verify” the lineage of the data. This verification can indicate whether the data tampering was performed outside the bounds of authorized operations, further ensuring any data or regulatory audits pass with flying colors.
SQL Server 2022 extends this ledger ability down from the cloud into the boxed product. Previously, if you needed to apply blockchain, you had to do it within the confines of the Microsoft Azure ecosystem. Now you can do it in your own data center with the release of SQL Server 2022, which provides further means to ensure data security and integrity.
If you currently operate within a regulated silo, such as in financial or medical fields, it may be worth examining if SQL Ledger is a suitable security solution.
When Is the SQL Server 2022 Release Date?
A major question still looming is when will Microsoft SQL Server 2022 become generally available (GA)? While it’s officially unknown at the moment, we do know the name implies it will become GA sometime this calendar year, and Microsoft has alluded to this in announcements, with no guarantees. Given we’re already about halfway through the year and the fact Microsoft usually announces new releases at major events, one could conclude SQL Server 2022 will go to GA during Microsoft Ignite, held later this fall.
What Is the Future of SQL Server?
Microsoft continues to push the envelope with the features and capabilities of its SQL Server product line. There’s a continued evolvement into more cloud-centric solutions while maintaining its grassroots history for the on-premises installs. I suspect future releases of SQL Server will bring forth tighter integration with Azure SQL and continued evolvement of the Intelligent Query Processing family of features. While some would argue the need for a dedicated DBA is diminishing, it’s my opinion the need for DBAs is more crucial than ever before.
How Can You Prepare for SQL 2022?
SQL Server 2022 is currently in public preview, having moved out of private preview within the last month or so (as of June 2022), meaning you can download it, take it for a test drive, and ‘kick the tires.’ And as you examine all the shiny new enhancements Microsoft is doing with their SQL Server platform, make sure to look at product offerings by SolarWinds. SolarWinds® Database Performance Analyzer (DPA) and SQL Sentry® make a fantastic tool combination to help ensure all aspects of your SQL Server 2022 implementation are covered. DPA database monitoring can assist with optimal query performance, while SQL Sentry can keep an eye on the overall health of the SQL instance. Together, these tools are designed to help you maintain the well-being of your SQL Server 2022 instances.
