Problem
SQL Server 2022 is on its way and there are a lot of new features that could be of interest. Check out this summary of new features and things to investigate.
Solution
Beginning with SQL Server 2012, Microsoft has launched a new release of SQL Server every two years at most. This puts IT professionals in a situation where we must decide if updating to the latest version is worth the time and costs. To help with this decision, this tip provides a list of key features (by category) that are new or have been enhanced in the latest version, SQL Server 2022.
Analytics
Azure Synapse Link for SQL Server
This feature allows you to run near real-time analytics with minimum impact on the operational workload and reduces complexity because it doesn’t require using ETL processes to deal with data movement.
Object Storage Integration
Currently, you are not limited to using Azure Storage. SQL Server 2022 supports S3-compatible object storage to take backups to URLs and Data Lake virtualization.
Always On and High Availability
Link to Azure SQL Managed Instance
SQL Server 2022 allows you to connect SQL Server instances to an Azure SQL Server managed instance, allowing you to offload read-only workloads to the cloud.
Contained Availability Groups
You will be able to create an availability group with its own users, logins, permissions, and SQL Agent jobs. To date, if you wanted to grant access for a new login to an availability group database, you had to create the user on all servers that host the availability group.
Distributed Availability Group
There will be added support to modifying the required synchronized secondaries to commit and an increased number of TCP connections on links with high latencies.
There are also improvements in the thread usage by availability groups, specifically in the parallel redo. Now each database can benefit from parallel redo. Additionally, now redo records are batched to improve speed.
Security
SQL Server 2022 integrates with Microsoft Defender for Cloud in your VMs if you have installed the SQL Server IaaS Agent extension (SqlIaasExtension).
Integration with Microsoft Purview
Microsoft Purview is a data governance solution that eases data administration across on-premises and multi-cloud environments. Now you can apply access policies, including the new "SQL Performance Monitor", and "SQL Security Auditor" roles to instances.
Azure Active Directory Authentication
Now we can connect to SQL Server using Azure AD authentication on Linux and Windows servers if they are Azure Arc-enabled servers. Azure Arc-enabled servers allow you to manage Windows and Linux physical and virtual servers hosted outside of Azure.
Always Encrypted Improvements
Performance improvements due to added support for multithreading inside the enclave and key caching.
New Server Roles
SQL Server 2022 added new server-level roles that are very handy to DBAs.
Dynamic Data Masking
Now the UNMASK permission adds more granularity allowing you to grant this permission at the database, schema, table, and even column levels.
Improvements in Certificate and Keys Management
SQL Server 2022 added support to create certificates from PFX files. Additionally, you can back up and restore certificates and symmetric keys from Azure BLOB storage. Another improvement is that system-generated certificates now use RSA-3072 by default.
Networking
SQL Server 2022 makes encryption mandatory when using MS-TDS 8 and TLS 1.3.
Ledger
This new version of SQL Server introduces an implementation of blockchain. I suppose that the first thing that comes to mind when someone talks about blockchain is Bitcoin and cryptocurrencies. Still, other uses for this technology make it useful to guarantee the integrity of data.
Query Store Related
SQL Server 2022 allows us to enable Query Store on secondary replicas. This is very useful when you have different workload types amongst the primary and the secondary replicas. For example, if your primary role supports an OLTP solution and the secondaries are used for reporting, now you can have two distinct query stores for the different workload types.
Another interesting new feature of Query Store is the possibility to set up query hints for the queries in the Query Store using the sp_query_store_set_hints and sp_query_store_clear_hints stored procedures.
Also, with this release of SQL Server, Microsoft includes a set of enhancements relying on Query Store that they name Intelligent Query Processing (IQP). The most important, in my opinion, is Query Processing Feedback which provides feedback for the following settings that affect query execution:
- Cardinality estimation
- Degree of parallelism (DOP)
- Memory grant
But, to take advantage of this feature, you will need to enable Query Store in read-write mode.
Other items included in Intelligent Query Processing are Optimized Plan Forcing and Parameter Sensitive Plan optimization.
Performance Related
SQL Server 2022 extends segment elimination capabilities to string, binary, guid, and datetimeoffset for scale greater than two.
Another interesting new feature is the introduction of an XML compression option in the CREATE TABLE and CREATE INDEX commands. This option allows us to compress off-row XML data for both XML columns and indexes.
Speaking of indexes, another very useful feature is included in this release: Resumable add table constraints. As its name suggests, now we can pause and resume the creation of constraints if they were created using the RESUMABLE = ON option. This option requires that you add the ONLINE = ON option.
Yet another index-related improvement, but this time on columnstore indexes, is the introduction of ordered clustered columnstore indexes to improve performance for queries based on ordered column predicates. This is done by sorting the data prior to index compression.
There are also changes in the way SQL Server manages files. Concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages reduce page latch contention while allocating/deallocating data pages and extents. The number of Virtual Log Files (VLF) created if the log file growth is less than or equal to 64 MB and more than 1/8 of the current log size is now set to 1 VLF instead of the 4 VLFs on previous SQL Server versions.
Hardware Integration
This latest version of SQL Server uses the CPUs AVX-512 instruction set to improve batch mode operations. This is something to consider when choosing the right CPU for your new server.
Additionally, a new feature named Integrated Acceleration & Offloading provides a framework for offloading specific SQL Server workload compute to hardware devices. At this time, it works with Intel’s QuickAssist Technology (QAT) and allows for offloading backup (restore) compression (decompression) tasks. See Intel QAT: Performance, Scale, and Efficiency for more information.
Next Steps
- Download your copy of SQL Server 2022 today: SQL Server 2022 Preview.
- This version of SQL Server also includes T-SQL enhancements. Aaron Bertrand shares his favorites in this tip: My Favorite T-SQL Enhancements in SQL Server 2022.
- Stay tuned to the SQL Server 2022 Tips category on MSSQLTips.com for more SQL Server 2022 tips and tricks.
