Joey on SQL Server

What's New in SQL Server 2019: A Closer Look at the Top New Features

Microsoft debuted SQL Server 2019 at Ignite, but a more technically detailed picture of the next-gen database platform emerged only later at the PASS Summit. Here are some of the standout additions and improvements.

• By Joey D'Antoni
• 12/04/2018

Microsoft introduced SQL Server 2019 as a preview at its Ignite conference in September. However, a lot of new features were still in development at that time.

There were a few more announcements made at November's PASS Summit in Seattle covering new product features and the release of SQL Server 2019 CTP 2.1. While Ignite is more of a generalist IT audience, the audience at the PASS Summit is more DBA-focused, so the keynotes there tend to be deeper technically (specifically around SQL Server) than those at Ignite.

In this year's PASS Summit keynotes, Microsoft dug a little deeper into the features in SQL Server 2019. We wrote broadly about those announcements here, but for this column, I wanted to cover some important features in greater detail.

Always On Availability Groups
Microsoft introduced Always On Availability Groups in SQL Server 2012, and has been making improvements to the feature in each release since.

One of the biggest complaints most DBAs have about this feature, which allows multiple copies of a database to be replicated across multiple servers, is that there was no support for the system databases MSDB and Master, which contain log-ins and SQL Server Agent jobs, schedules and notifications. There were a number of community-provided workarounds for this. My personal favorite is DBATools, which runs PowerShell to keep log-ins and jobs synchronized between the availability group member nodes.

In SQL Server 2019, Microsoft is correcting this by addressing both Master and MSDB, which will simplify the administration of availability groups. Another new feature is the ability to have up to five synchronous replicas, up from the previous limit of three. You can have a total of nine replicas in your availability group, with the option to extend that further using distributed availability groups.

Accelerated Data Recovery
There are two features I'm writing about here that I thought were sheer magic the first time I saw them.

If you have ever killed a long-running transaction and waited...and waited...and waited for that transaction to roll back, this feature will be of interest to you. SQL Server's process for this was to identify the oldest page log sequence number (LSN), then perform an undo and a redo phase. The undo phase involved traversing the transaction log file in reverse -- which, for large transactions, can take nearly the same amount of time as the original transaction. Another side-effect of this behavior is that the transaction log cannot be truncated while this rollback is taking place, which can result in disk space issues.

Accelerated Database Recovery (ADR) is currently available in public preview in Azure SQL Database, as well as Azure SQL Data Warehouse. It will soon be on by default for all existing and new databases and data warehouses in Azure. ADR will also be available in public preview in one of the upcoming CTP builds of SQL Server 2019, and is expected to be in the release-to-manufacturing (RTM) version of SQL Server 2019.

The way it works is by using a persisted version store in the database itself, as opposed to the TempDB version store for Read Committed Snapshot Isolation. This works in conjunction with a process called logical revert, which reads the version store and provides instantaneous rollback and undo. There are also some in-memory and clean-up components that provide background processing to support the overall process.

This feature does have some overhead, but any mission-critical system that has to be patched regularly or simply has long-running transactions that could be rolled back will benefit from the new behavior.

Scalar Function Inlining
The other feature that I refer to as simply magic was referenced in this Microsoft Research whitepaper released in the spring of 2018. The first time I tested it, I was blown away.

Scalar functions that can be included in a query is a concept that is very familiar to developers, who are used to writing in their object-oriented code. Sadly, this has been a problematic design pattern in SQL Server. The scalar functions are executed in a black box, giving no insight into the underlying query operations to process them and -- through a combination of factors existing in the SQL Server codebase -- are limited in performance. The two biggest problems are the fact that these functions are limited to single-threaded execution and, more importantly, are executed row by row.

What this scalar function inlining feature does is transform that row-by-row execution into a subquery that is optimized inline and shown in the query plan. The performance effects are quite dramatic. At the PASS Summit, I did a demo using a query that called an inline scalar function three times. In older compatibility modes, the query took around 15 seconds. When I switched the compatibility mode to 150 (using SQL Server 2019), the same query took 900 milliseconds and the execution plan showed all of the detail of the function's execution.

Other New Features
There are number of other new features, including online columnstore index builds and rebuilds, static data masking (which allows for masked data to be persisted to other environments), and enhancements to Always Encrypted to allow more operations to be performed on encrypted data in order to make the feature more useful.

There are also many subtle enhancements to a wide array of features, including persistent memory options for data storage, and broader external database (Oracle, Teradata and MongoDB) support for PolyBase, which aligns to the targets for SQL Server Big Data clusters.

I would expect many smaller announcements throughout SQL Server 2019's development process. While Microsoft has painted with broad brush strokes to tell us about new features, much of the finer details are yet to come. However, as you've seen in this article, or if you have watched the PASS Summit keynotes, there are some major improvements coming to the frequently used functional areas in SQL Server.