Joey on SQL Server

Introducing SQL Server 2019: What's New in the Public Preview

Microsoft announced the latest preview of its next-gen SQL Server product at Ignite. Here's a rundown of everything the new release promises, from performance enhancements to security and availability perks.

• By Joey D'Antoni
• 09/24/2018

Microsoft introduced the public preview of SQL Server 2019 on Monday at the start of the 2018 Ignite conference. While the big news is around SQL Server Big Data clusters, there are a number of major improvements that continue to improve and transform the centerpiece of the Microsoft data platform stack.

You will note most of these improvements are to the relational database engine as development of the BI stack is no longer tied directly to the release of the database engine. Somewhat like SQL Server 2017, this release of SQL Server has a number of minor enhancements -- with one major new feature.

Database Performance Enhancements
With every release, Microsoft makes some internal optimizations that enhance performance. Sometimes these are edge case features, while sometimes they help everyone.

One of these optimizations was released in SQL Server 2017. When a database executes a query, it needs a certain amount of memory available to perform operations like sorting and joining data to deliver results to the user. The amount of memory the engine allocates to a given query is determined mainly by the statistics maintained on the data. A query that needs to join 5 billion rows is going to get a lot more memory granted than one that needs to join five rows. Sometimes the statistics are wrong, which leads to concurrency or performance issues.

Microsoft introduced a fix for this in SQL Server 2017, where subsequent executions of a given query would have their processing memory adjusted based on the runtime statistics of the previous execution. This was a really big deal, except that it had one major limitation: It only supported queries that used batch execution mode, which required the use of a columnstore index (which works best with analytical workloads, more than transaction processing). Now, in SQL Server 2019 dynamic memory grants will be available to all queries.

While we are talking about batch execution mode: As mentioned, before this feature -- which processes data in chunks of around 1,000 rows, making it exceptionally fast for operations like aggregate functions (averages, sums, standard deviations, etc. -- has only been available for columnstore indexes. With SQL Server 2019, batch mode over row store is available. I have done some limited testing with this in early releases and was pretty impressed with the results I had on aggregation queries.

A common data warehouse performance issue relates to distinct counts for an item. Typically, within a database it is extremely expensive to generate a list of distinct (i.e., unique within the column) values from a large table. This cost is seen more frequently in BI scenarios because of the requirement to report on things like the number of products sold to each customer, or the number of sales records by product. SQL Server 2019 introduces new a functionality called "approximate count distinct" that uses some statistical magic to provide close-to-correct data where it can be used, with much faster results.

Another performance benefit and hardware trend in recent years is the adoption of persistent memory, which is effectively block-level storage that writes at the speed of RAM. This notion is kind of a holy grail for databases, which are frequently performance-limited by the speed of their underlying storage. SQL Server 2016 introduced support for persistent memory -- or NV-DIMM, as it is called for the tail of the transaction log, which means the latest writes to happen to a database could happen faster.

In SQL Server 2019, support for these devices is extended, namely for Intel Optane DC NV-DIMMs, on Windows Server 2019. This allows all database objects to be stored on persistent memory as normal block-based storage.

Users of SQL Server on Linux should not feel left out. There is a specific enhancement to the Linux platform that allows database files to be mapped directly to memory, skipping expensive kernel calls to the storage stack. While Windows and Linux have very different storage engines, it is clear Microsoft is moving to a direction of databases that live completely in persisted RAM for ultimate performance.

Security
Always Encrypted, which is Microsoft's solution for encrypting data within SQL Server and keeping it private to everyone except the application user, debuted in SQL Server 2016 and was made available to all editions of the product with SQL Server 2016 Service Pack 1.

Always Encrypted is great at keeping your data secured, but there are limits on the operations you can perform on this encrypted data. For example, you can only query using equality values (WHERE 1=1) and you do not have the ability to perform mathematical operations on the encrypted values of the data.

Using a new technology called "Secure Enclaves," data that is encrypted using Always Encrypted can now be compared in range scans and LIKE queries, and even operated on using aggregate functions. An enclave appears as a black box to the process that is calling it, so even though this data is decrypted, its clear text values are never seen by the calling process.

Availability
There a number of enhancements to the Always On Availability Group stack.

As part of SQL Server Big Data clusters, there is support for Kubernetes. Additionally, new features like connection redirection and enhanced database health checks will help provide higher levels of uptime for customers.

Introduced in SQL Server 2017, Resumable Index Maintenance, which allows DBAs to pause and resume the rebuilding of large indexes, which can get fragmented over time, has a new beneficial companion feature called Resumable Online Index Creation. This allows an administrator to create part of a new index (during an outage window, presumably) to be resumed at a later time. Of course, there is some storage cost associated with this, but for larger tables, it can make the process of creating a new index much easier.

SQL Server 2019 is still in the early preview stage. There are still many more things to come between now and the time SQL Server 2019 becomes generally available. However, it is clear that Microsoft continues to make big investments in the data platform and is working hard to keep it available and consistent across server and data platforms, expanding the broader audience for data services.