What’s New in SQL Server 2019?

SQL Server 2019 is the latest public release of SQL Server. SQL Server 2019 builds upon and improves some of the features introduced in earlier versions of SQL Server and also introduces several new features.

Things to Love About SQL Server 2019

There’s a lot to love about SQL Server 2019—in particular, Microsoft’s continued focus on security, flexibility, and maintaining high availability. The latest version of SQL Server has significantly improved and enhanced key areas, including: 

• Data analysis
• Ability to choose programming language and platform
• Improved scalability and performance
• Data security
• Enhanced reporting

SQL Server 2019 Key Features

You can find in-depth details of SQL Server 2019’s features and functionality in the Microsoft documentation, but we’ve summarized a few key features from that document in this spotlight on SQL Server 2019.

SQL Server big data clusters

SQL Server big data clusters provide an environment for working with large sets of data. According to Microsoft, SQL Server 2019’s big data clusters let users:

• Deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes
• Read, write, and process big data from Transact-SQL or Spark
• Combine and analyze high-value relational data with high-volume big data
• Query external data sources
• Store big data in HDFS managed by SQL Server

Intelligent query processing

Intelligent query processing is a set of features that improves the performance of existing workloads running with compatibility mode 150. Intelligent query processing requires minimal implementation effort and provides many performance benefits. Microsoft’s documentation outlines the following features:

Row mode memory grant feedback: Expands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators. 

Batch mode adaptive joins on rowstore: Enables batch mode execution without requiring columnstore indexes.

Scalar UDF inlining: Automatically transforms scalar UDFs into relational expressions and embeds them in the calling SQL query. 

Table variable deferred compilation: Improves plan quality and overall performance for queries that reference table variables. 

• Approximate query processing with APPROX_COUNT_DISTINCT: For scenarios in which absolute precision isn't important but responsiveness is critical, APPROX_COUNT_DISTINCT aggregates across large datasets while using fewer resources than COUNT(DISTINCT()) for superior concurrency.

Accelerated database recovery

New in SQL Server 2019, accelerated database recovery increases availability with a redesigned SQL Server database engine recovery process. The new recovery feature enables a database to complete the recovery process much faster after a failover, long-running transaction rollback, or other non-clean shutdown.

Monitoring improvements

Lightweight profiling is now enabled by default in SQL Server 2019. If you don’t want this feature enabled, a new database-scoped configuration, LIGHTWEIGHT_QUERY_PROFILING, can be used to disable this setting at the database level.

Other new monitoring features highlighted by Microsoft include: 

WAIT_ON_SYNC_STATISTICS_REFRESH: This new wait type in sys.dm_os_wait_stats dynamic management view shows the accumulated instance-level time spent on synchronous statistics refresh operations.

Sys.dm_exec_query_plan_stats: This new DMF returns the equivalent of the last known actual execution plan for all queries.

LAST_QUERY_PLAN_STATS: This new database-scoped configuration enables sys.dm_exec_query_plan_stats.

• sys.dm_db_page_info(database_id, file_id, page_id, mode): This new DMF returns information about a page in a database. 

Updates to availability groups

Availability is a key reason SQL Server is so popular. SQL Server 2019 has expanded its features and enhanced existing features to further ensure organizations’ databases are always highly available.

SQL Server’s Always On availability groups feature is a high availability/disaster recovery solution that maximizes the availability of a set of user databases for an enterprise. SQL Server 2019 offers these new and improved availability group features and functions that provide even greater database availability and reliability:

SQL Server 2019 increases the maximum number of synchronous replicas to five, up from three in SQL Server 2017.

In this version of SQL Server, Microsoft introduces secondary-to-primary replica connection redirection, which allows client application connections to be directed to the primary replica regardless of the target server specified in the connection string. 

Effective November 1, 2019, every SQL Server Software Assurance customer will have access to these three enhanced benefits for any SQL Server release that is still supported by Microsoft:

• Failover servers for high availability
• Failover servers for disaster recovery
• Failover servers for disaster recovery in Azure

You can learn more about these high availability and disaster recovery benefits in Microsoft’s official announcement. 

Database Experimentation Assistant

Database Experimentation Assistant (DEA) is a new A/B testing solution designed to help SQL Server customers evaluate which version of SQL Server fits best with their specific workload. 

DEA provides customers upgrading from earlier versions of SQL Server (2005 and newer) with analysis metrics, such as queries that have compatibility errors, degraded queries and query plans, and other workload comparison data.

DEA allows customers to capture and replay production database workloads through automated setup and has the ability to capture and replay on Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Linux.