Joey on SQL Server

SQL Server 2019 Is Here: A Roundup of Its Best Features

From Big Data clusters to faster database recovery, here are the biggest changes Microsoft has made to its new flagship database server.

• By Joey D'Antoni
• 11/04/2019

• Related: SQL Server 2019 Licensing: How Much Does It Cost and What's Included?

Microsoft announced the general availability of SQL Server 2019 and SQL Server Big Data Clusters at its Ignite conference on Monday. SQL Server 2019 builds on intelligent query processing features that were implemented in SQL Server 2017, and expands them to new areas of the database engine.

Let's take a deeper dive into some of some of the key new features.

Big Data Clusters and PolyBase
I've written a lot about Big Data Clusters and I think there's a lot of very interesting technology in the platform. The platform runs SQL Server on Kubernetes and directly connects SQL Server to Apache Spark for in-memory machine learning and advanced analytical functions. While this is a huge development, it is going to be a very big paradigm shift for many organizations to adopt, so I feel like the uptake will be slower.

The more interesting component of this feature set is a major enhancement to PolyBase that allows you to connect SQL Server to Oracle, MongoDB and Teradata natively, and offers ODBC connectivity to other data sources (for example, SharePoint lists). What this means is that you can write T-SQL queries to return data from tables within Oracle and have an optimized query. Microsoft calls this "data virtualization." One of the ways it can help you is by reducing the effort around your extract, transform and load (ETL) processes. 

PolyBase has also been added to SQL Server Standard Edition, which means it's available to more customers.

Intelligent Query Processing
SQL Server 2016 introduced a wonderful feature called the Query Store -- or, as I like to call it, the flight data recorder for SQL Server. This feature lets you have runtime information and execution plans for all of your queries, allowing a database administrator to very quickly isolate the root cause of a performance issue.

SQL Server and Azure SQL Database have built intelligence onto this feature by adding automatic query tuning for execution plans that regress in performance. SQL Server 2019 takes this a few steps further by changing the amount of memory that is granted to query for sorting and comparison, which can cause both throughput and performance issues.

SQL Server 2019 also introduces scalar function inlining and deferred compilation for table variables. Both features were performance anti-patterns in earlier versions of SQL Server, as they led to incorrect row estimates in query processing. In my testing, I saw up to 10x performance improvements for queries using scalar functions in SQL Server 2019. There are number of other features in this space, which we'll cover in more detail in an upcoming column.

Smarter Monitoring
The Query Store feature was a major benefit, but on some systems, it caused some resource contention issues. This was particularly impactful for customers who ran exclusively dynamic SQL (where each query issued against the database is a unique hash value). SQL Server 2019 introduces a custom capture option that enables you to fine-tune data collection to avoid this issue.

Additionally, there are a number of enhancements around execution plan collection. These allow you to capture actual execution plans, which lets you examine the row counts used by a query and compare them to estimates that the query optimizer used. In turn, this can help you quickly isolate a performance problem. You could always do this before using profiler or extended events functionality, but the overhead of performing these captures were extremely high.

Optimized Insert Performance
SQL Server has long suffered from a bottleneck when trying to insert sequential records into a table at very high volume. This is because of contention in memory, as only one worker thread can access a data page at a given moment. The in-memory OLTP feature was designed to remediate this issue, but it is not 100 percent compatible with all data types so it cannot be used everywhere.

SQL Server 2019 introduces an optional feature called "optimize_for_sequential_key" that reduces the effect of these bottlenecks. Microsoft quotes up to 40 percent performance gains with this feature, and I can add that I have personally seen these gains with a customer as the feature was being developed. This feature can be added at the table level and should only be used for workloads that will benefit from it.

Another performance improvement comes in SQL Server's temporary workspace, TempDB. Taking advantage of the in-memory OLTP feature, which eliminates a bottleneck at the data latching layer, memory optimized TempDB metadata allows for more overall throughput to the busy TempDB.

Faster Database Recovery
Anyone who's ever been a database administrator has felt the pain of restore getting to 100 percent and then sitting there for a long time afterward, just waiting and waiting for the process to actually complete. This is because of a process called crash recovery, in which transactions that were in the database transaction log but not in the database backup have to be played back to the database.

Accelerated database recovery changes this process by implementing a version store to allow transactions to be played back much faster after a restore or server restart. There is a minor performance overhead to this (1 to 2 percent in customer testing), but the effects can be dramatic. One sample rollback that took 90 seconds went down to 1 second.

The Rest of the Features
There are a lot of interesting features in this release, including resumable index creation, which means you can create an index in maintenance windows or resume building after a server failure. The Always Encrypted feature has also received a big enhancement called secure enclaves, which allows the database engine to operate on encrypted data in T-SQL without the data ever being visible to a user or administrator.

You will notice I didn't mention Azure in this column. While many of these features have been turned on in Azure SQL Database for months, Microsoft is still committed to building on-premises SQL Server as it's a big product for the company.

Finally, Microsoft also announced an update for SQL Server 2019, so if your organization typically waits for the first patch of a newly released product, you can install it today.