What's new in SQL Server 2022 (16.x) Preview

Applies to: yesSQL Server 2022 (16.x) Preview

SQL Server 2022 (16.x) Preview builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud environments, and operating systems.

The following video introduces SQL Server 2022 Preview.

For additional video content, see What's new in SQL Server.

This article summarizes the new features and enhancements for SQL Server 2022 (16.x) Preview.

Get SQL Server 2022 Preview

Get SQL Server 2022 Preview Evaluation Edition.

For more information and known issues, see SQL Server 2022 (16.x) Preview release notes.

For the best experience with SQL Server 2022 (16.x) Preview, use the latest tools.

Community technology preview release

This release is community technology preview (CTP) 2.0. CTP 2.0 is the first public preview release. Previous releases (CTP 1.x) were available to select participants in the early adopter program (EAP).

This release:

  • Is available as Evaluation Edition. It's available for a 180 day trial period, and includes all of the capabilities of Enterprise Edition.
  • On Azure Virtual Machines, it's available as Developer Edition. It's available for a 180 day trial period via a SQL Server on Azure Virtual Machines marketplace image.
  • For SQL Server 2022 Preview on Linux, see Release notes for SQL Server 2022 (16.x) Preview on Linux.
  • Doesn't include support from Microsoft, except for select EAP customers.

After you experience SQL Server 2022 Preview, you're welcome to submit feedback about the product.

The following sections provide an overview of these features.

Analytics

New feature or update Details
Azure Synapse Link for SQL Get near real time analytics over operational data in SQL Server 2022 (16.x) Preview. With a seamless integration between operational stores in SQL Server 2022 (16.x) Preview and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables you to run analytics, business intelligence and machine learning scenarios on your operational data with minimum impact on source databases with a new change feed technology.

For more information, see What is Azure Synapse Link for SQL? (Preview) - Azure Synapse Analytics.
See also, Known issues.
Object storage integration SQL Server 2022 Preview introduces new object storage integration to the data platform, enabling you to integrate SQL Server with S3-compatible object storage, in addition to Azure Storage. The first is backup to URL and the second is Data Lake Virtualization.

Data Lake Virtualization integrates PolyBase with S3-compatible object storage, adds support for to querying parquet files with T-SQL.

Availability

New feature or update Details
Link to Azure SQL Managed Instance Connect your SQL Server instance to Azure SQL Managed Instance. See Link feature for Azure SQL Managed Instance (preview). To experience this feature, you can register here.
Contained availability group Create an Always On availability group that:
- Manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level.
- Includes specialized contained system databases within the availability group. For more information, see What is a contained availability group?
Distributed availability group - Changing REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT is supported. For more information, visit ALTER AVAILABILITY GROUP (Transact-SQL)
- Now using multiple TCP connections for better network bandwidth utilization across a remote link with long tcp latencies.
Improved availability groups - ParallelRedoThreadPool : Instance level thread pool shared with all databases having redo work. With this, each database can take the benefit of parallel redo. Limited to max 100 threads limit earlier.
- Parallel Redo Batch Redo - Redo of log records are batched under one latch improving speed. This helps both, catchup redo and crash recovery redo.
Improved backup metadata backupset system table returns last valid restore time. See backupset (Transact-SQL).

Security

New feature or update Details
Microsoft Defender for Cloud integration Protect your SQL servers using the Defender for SQL plan. Defender for SQL plan requires that SQL Server Extension for Azure is enabled and includes functionalities for discovering and mitigating potential database vulnerabilities and detecting anomalous activities that could indicate a threat to your databases. Learn more on how Defender for SQL can protect your entire database estate anywhere: on-premises, hybrid, and multi-cloud environments.
Microsoft Purview integration Apply Microsoft Purview access policies to any SQL Server instance that is enrolled in both Azure Arc and the Microsoft Purview Data use management.

- Newly introduced SQL Performance Monitor, and SQL Security Auditor roles align with the principle of least privilege using Microsoft Purview access policies.

Check out Access provisioning by data owner for SQL Server on Azure Arc-enabled servers for details.
Ledger The ledger feature provides tamper-evidence capabilities in your database. You can cryptographically attest to other parties, such as auditors or other business parties, that your data hasn't been tampered with. See Ledger.
Azure Active Directory authentication Use Azure Active Directory (Azure AD) authentication to connect to SQL Server.
Always encrypted with secure enclaves Enable in-place encryption and richer confidential queries. Support for confidential queries with JOIN, GROUP BY, and ORDER BY. Improved performance. See Always Encrypted with secure enclaves.
New permissions & roles Enable least privileged access for administrative tasks with new built-in server-level roles.
Dynamic data masking Granular UNMASK permissions for Dynamic Data Masking.
Support for PFX certificates, symmetric key enhancements, and other crypto improvements New support for certificate and key backup and restore integration scenarios with Azure Blob Storage service. This enables adherence to security best practices and compliance standards guidelines that prohibit the usage of insecure or deprecated algorithms like RC4. Additional improvements to enhance default cryptography in SQL Server to meet the evolving threat landscape, including but not limited to, system-generated certificates now have a default strength of RSA-3072.

Added BACKUP SYMMETRIC KEY and RESTORE SYMMETRIC KEY.
Support MS-TDS 8.0 protocol New MS-TDS protocol iteration. See TDS 8.0 and TLS 1.3 support:
- Makes encryption mandatory
- Aligns MS-TDS with HTTPS making it manageable by network appliances for additional security
- Removes MS-TDS / TLS custom interleaving and enables usage of TLS 1.3 and subsequent TLS protocol versions.

Performance

New feature or update Details
Query Store on secondary replicas Query Store on secondary replicas enables the same Query Store functionality on secondary replica workloads that is available for primary replicas. Learn more in Query Store for secondary replicas.

For more information, see Query Store improvements later in this article.
Query Store hints Query Store hints leverage Query Store to provide a method to shape query plans without changing application code. Previously only available on Azure SQL Database and Azure SQL Managed Instance, now are available in SQL Server 2022 Preview. Requires enabling Query Store.
Memory grant feedback Memory grant feedback adjusts the size of the memory allocated for a query based on past performance. SQL Server 2022 preview introduces Percentile and Persistence mode memory grant feedback. Requires enabling Query Store.

- Persistence: A capability that allows the memory grant feedback for a given cached plan to be persisted in the Query Store so that feedback can be reused after cache evictions.
- Percentile: A new algorithm improves performance of queries with widely oscillating memory requirements, using memory grant information from several previous query executions over, instead of just the memory grant from the immediately preceding query execution.
Ordered clustered columnstore index Ordered clustered columnstore index (CCI) sorts the existing data in memory before the index builder compresses the data into index segments. This has the potential of more efficient segment elimination, resulting in better performance as the number of segments to read from disk is reduced. For more information, see CREATE COLUMNSTORE INDEX (Transact-SQL).
Also available in Synapse Analytics. See Query performance.
In-memory OLTP management Improve memory management in large memory servers to reduce out-of-memory conditions.
Parameter sensitive plan optimization Automatically enables multiple, active cached plans for a single parameterized statement. Cached execution plans accommodate largely different data sizes based on the customer-provided runtime parameter value(s). For more information, see Parameter Sensitivity Plan optimization.
XML compression XML compression provides a method to compress off-row XML data for both XML columns and indexes, improving capacity requirements. For more information, see CREATE TABLE (Transact-SQL) and CREATE INDEX (Transact-SQL).
Improved optimization SQL Server 2022 Preview leverages new hardware capabilities - including the Advanced Vector Extension (AVX) 512 extension to improve batch mode operations.
System page latch concurrency enhancements 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. These enhancements apply to all user databases and especially benefit tempdb heavy workloads.
Buffer pool parallel scan Improves the performance of buffer pool scan operations on large-memory machines by utilizing multiple CPU cores. Learn more about Operations that trigger a buffer pool scan may run slowly on large-memory computers.
Degree of parallelism (DOP) feedback A new database scoped configuration option DOP_FEEDBACK automatically adjusts degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues. Similar to optimizations in Azure SQL Database. Requires Query Store enabled. See Configure the max degree of parallelism Server Configuration Option.
Cardinality estimation feedback Identifies and corrects suboptimal query execution plans for repeating queries, when these issues are caused by incorrect estimation model assumptions. Requires Query Store enabled. See Cardinality Estimation (SQL Server).
Optimized plan forcing Uses compilation replay to improve the compilation time for forced plan generation by pre-caching non-repeatable plan compilation steps. Learn more in Optimized plan forcing with Query Store.
Virtual log file growth In previous versions of SQL Server, if the next growth is more than 1/8 of the current log size, and the growth is less than 64MB, four VLFs were created. In SQL Server 2022, this behavior is slightly different. Only one VLF is created if the growth is less than or equal to 64 MB and more than 1/8 of the current log size. For more information on VLF growth, see Virtual Log Files (VLFs).

Management

New feature or update Details
Setup attached to Azure Install Azure Arc agent via SQL Server at setup. For more information, see Install SQL Server from the Command Prompt.
Max server memory calculations During setup, the installation will configure max server memory to align with recommendations. See Server memory configuration options.
Accelerated Database Recovery (ADR) improvements There are several improvements to address persistent version store (PVS) storage and improve overall scalability. SQL Server 2022 implements a persistent version store cleaner thread per database instead of per instance and the memory footprint for PVS page tracker has been improved. There are also a number of ADR efficiency improvements, such as concurrency improvements that help the cleanup process to work more efficiently. ADR cleans pages that couldn't previously be cleaned due to locking.

See ADR improvements in SQL Server 2022 (16.x) Preview.
Improved snapshot backup support Adds Transact-SQL support for freezing and thawing I/O without requiring a VDI client. Create a Transact-SQL snapshot backup.
Shrink database WAIT_AT_LOW_PRIORITY In previous releases, shrinking databases and database files to reclaim space often leads to concurrency issues. SQL Server 2022 Preview adds WAIT_AT_LOW_PRIORITY as an additional option for shrink operations (DBCC SHRINKDATABASE and DBCC SHRINKFILE). When you specify WAIT_AT_LOW_PRIORITY, new queries requiring Sch-S or Sch-M locks aren't blocked by the waiting shrink operation, until the shrink operation stops waiting and begins executing. See Shrink a database and Shrink a file.
Asynchronous auto update statistics concurrency Avoid potential concurrency issues using asynchronous statistics update if you enable the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration.
Backup and restore to S3 compatible object storage SQL Server 2022 extends the BACKUP/RESTORE TO/FROM URL syntax by adding support for a new S3 connector using the REST API. See backup to URL.

Language

New feature or update Details
CREATE STATISTICS Adds AUTO_DROP option

Automatic statistics with low priority.
Time series functions You can store and analyze data that changes over time, using time-windowing, aggregation, and filtering capabilities.
- DATE_BUCKET
- GENERATE_SERIES

The following adds support to IGNORE NULLS and RESPECT NULLS:
- FIRST_VALUE
- LAST_VALUE
JSON functions - ISJSON (Transact-SQL)
- JSON_PATH_EXISTS (Transact-SQL)
- JSON_OBJECT (Transact-SQL)
- JSON_ARRAY (Transact-SQL)
SELECT ... WINDOW clause Determines the partitioning and ordering of a rowset before the window function, which uses the window in OVER clause is applied. See SELECT - WINDOW - (Transact-SQL).
Resumable add table constraints Supports pausing and resuming an ALTER TABLE ADD CONSTRAINT operation. Resume such operation after maintenance windows, failovers, or system failures.
T-SQL functions - GREATEST (Transact-SQL)
- LEAST (Transact-SQL)
- STRING_SPLIT (Transact-SQL).

Tools

New feature or update Details
Azure Data Studio Get the latest release at Download and install Azure Data Studio. The latest release includes support for SQL Server 2022.
Distributed Replay SQL Server setup no longer includes the Distributed Replay client and controller executables. These will be available, along with the Admin executable, as a separate download
SQL Server Management Studio SSMS version 19.0 is now available and is the recommended version of SSMS for SQL Server 2022. Download SQL Server Management Studio (SSMS).
SqlPackage.exe Version 19 of SqlPackage provides support for SQL Server 2022. Get the latest version at Download and install sqlpackage.
VS Code The latest release of VS Code, version 1.67, supports SQL Server 2022. Get it at https://code.visualstudio.com/.

SQL Machine Learning Services

Beginning with SQL Server 2022, runtimes for R, Python, and Java, are no longer installed with SQL Setup. Instead, install any desired custom runtime(s) and packages. For more information, see Install SQL Server Machine Learning Services (Python and R) on Windows or Install SQL Server Machine Learning Services (Python and R) on Linux.

Additional information

This section provides additional information for the features highlighted above.

Query Store improvements

Query Store helps you better track performance history, troubleshoot query plan related issues, and enable new capabilities in Azure SQL Database and SQL Server 2022. Additionally, Query Store hints are a preview feature in SQL Server 2022 (16.x) Preview. To use Query Store features, enable Query Store. For instructions, see Enable the Query Store.

  • For databases that have been restored from other SQL Server instances and for those databases that are upgraded from an in-place upgrade to SQL Server 2022, these databases will retain the previous Query Store settings.

  • For databases that are restored from previous SQL Server instances it's recommended to enable Query Store and separately evaluate the database compatibility level settings as some Intelligent Query Processing features are enabled by the compatibility level setting.

If there's concern about the overhead Query Store may introduce, administrators can leverage custom capture policies to further tune what the Query Store captures. Custom capture policies are available to help further tune Query Store captures. Custom capture policies can be used to be more selective about which queries, and query details are captured. For example, an administrator may choose to capture only the most expensive queries, repeated queries, or the queries that have a high level of compute overhead. Custom capture policies can help Query Store capture the most important queries in your workload. Note that except for the STALE_CAPTURE_POLICY_THRESHOLD option, these options define the OR conditions that need to happen for queries to be captured in the defined Stale Capture Policy Threshold value. For example, these are the default values in the QUERY_CAPTURE_MODE = AUTO:

...
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = ( 
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, 
EXECUTION_COUNT = 30, 
TOTAL_COMPILE_CPU_TIME_MS = 1000, 
TOTAL_EXECUTION_CPU_TIME_MS = 100 
)
...

SQL Server Analysis Services

This release introduces new features and improvements for performance, resource governance, and client support. For specific updates, see What's new in SQL Server Analysis Services.

See also

Get help