Top 10 New Features of SQL Server 2019

by Warner Chaves on Dec 22, 2020 12:00:00 AM

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we bring forward a popular post from the past. We originally published today’s post on October 29, 2019. It's no secret to anyone that software release cycles are more aggressive than ever. Now that the cloud has become ubiquitous as a strategic component of IT services, we are spoiled by a constant release of new features and services available to us. It follows then that on-premises or "retail-delivered" software is also getting more frequent releases as customer expectations grow. This is how, over the last four years, we've had three major releases of SQL Server: 2016, 2017 (with the big milestone of SQL on Linux) and now SQL 2019. SQL 2019 is shaping up to be a very powerful evolutionary step to the features that were introduced in SQL 2017. The product also brings its own set of brand-new capabilities that continue to position it as a "one-stop" software platform for all your data needs. With this in mind, and considering how much the product contains nowadays, I have compiled this list of my top favorite features of SQL 2019! Now, of course, this is my post, and my opinion, and the list is not exhaustive. For the full list, refer to the Microsoft documentation. As you will see, my favorites are mostly technical features that will help developers and DBAs in their daily functions. Also, a quick disclaimer — all the images are from Microsoft's own SQL Server documentation. I'm sure your top 10 is likely to be very different than my top 10 so make sure you check it all out in detail! Here is my list:

1. Intelligent Query Processing Enhancements

What they are: This is a set of enhancements that affect the behavior of the Query Optimizer; the component inside SQL Server that generates the execution plans for queries. This includes dynamic memory grants for rowstore tables, table variable deferred compilation, batch mode on rowstore and more. Why this matters: These are all behind-the-scenes improvements on the Query Optimizer that will improve the quality of the plans for all applicable queries. This means better performance overall after doing the upgrade. Cost of adoption: Test your problem queries on a development instance to verify the improvements. That's pretty much it. It's one of those great improvements that works with no big changes required from the customer (I really like those as you will see from this list). The Intelligent Query Processing feature family.

The Intelligent Query Processing feature family.

2. Accelerated Database Recovery (ADR)

What this is: This is a completely new way for SQL Server to perform database recovery in the event of a transaction rollback, an instance restart or an availability group failover. Instead of the sometimes-unpredictable and less-than-desired time spent waiting for the database recovery to run, the SQL team has redeveloped how recovery works and has dramatically decreased how long this process takes. Why this matters: Anyone who has had to wait for a production SQL Server instance to rollback a long transaction or who has had an unfortunate crash during a large data operation knows how painful it is to just wait for recovery to be finished so you can get back in business. ADR will provide benefits for SLA and RTO all around. Cost of adoption: None. Activate it and enjoy (one of those again!). Recovery phase / transaction log / sLog (with ADR).

Recovery phase / transaction log / sLog (with ADR).

3. AlwaysEncrypted with secure enclaves

What this is: This is the next version of AlwaysEncrypted; the encryption technology introduced in SQL Server 2016 that allows transparent column encryption without giving administrators access to the decryption keys. One drawback of the first implementation is due to SQL Server not being able to decrypt the data, the queries on the SQL side couldn't do any computations or manipulation of the actual column values. Using the new secure enclaves technology, SQL Server can now securely encrypt a portion of memory to perform computations on these encrypted columns without ever exposing the unencrypted values to the rest of the processes (or administrators). Why this matters: Security matters and performance matters, as well. Database servers are best equipped for processing large amounts of data, so being able to have AlwaysEncrypted and also do complex manipulations is the best of both worlds. Cost of adoption: If you're already using AlwaysEncrypted then no big changes are necessary other than reconfiguring and re-encrypting the columns of interest. If you aren't using AlwaysEncrypted, now is a good time to investigate, test this feature and see if it's a good fit for your security requirements. AlwaysEncrypted with secure enclaves.

4. Memory-optimized Tempdb metadata

What this is: The SQL team has made optimizations to the tempdb code so some of the metadata that can be a bottleneck on tempdb heavy systems can rely completely on memory and be optimized for RAM access. Why this matters: Large-volume, large-scale environments that use a lot of tempdb run into this type of bottleneck. Usually, alleviating the use of tempdb requires some sort of refactoring. With this feature in place, it is possible to enable the metadata to sit in memory and be optimally accessed. Cost of adoption: Activate the feature and verify there is an improvement, not much more than that (another one!).

5. Query Store custom capture policies

What this is: Query Store is a great performance tuning and trending tool that allows for storing, measuring and fixing plan regressions inside a SQL Server database. One downside of using it, however, is it can sometimes store too much information, even for queries the DBA might not be interested in or for queries that were part of a system utility or monitoring tool. This new capability of custom policies means you can fine-tune exactly which queries you want Query Store to track based on their execution statistics such as how often they run, the CPU they consume and more. Why this matters: Query Store is a great feature but it is not so useful if it consumes a lot of resources and if it's too bloated to be effective for the DBA. This feature will allow fine-tuning so it's always efficient, lean and easy to use for fixing plan issues. Cost of adoption: You'll need to sit down and see what type of execution conditions you want to use as a filter for your Query Store. Implementing it is just a matter of using new syntax after that.

6. Verbose truncation warnings

What this is: Every single T-SQL developer knows the pain and grind of getting a truncation error. Some value somewhere doesn't fit under a new data type, but you don't get any details at all. Then it's a matter of trial and error until you finally figure out which value is the offending one. Not the best experience for what should be a straightforward issue to solve! Why this matters: Because mental sanity matters. These new messages give you all the details of the data truncation issue so you can just fix it and get on with your day! Cost of adoption: None. It's the new default (loving all of these)!

7. Resumable index build

What this is: SQL Server now has the capability to stop an index rebuild operation in progress, keep the work that has been done so far, and resume at some other point in time. Why this matters: For some folks, index rebuilds are still necessary and they consume so many resources that even with the ONLINE option, they still have to deal with the reality of maintenance windows. The problem, however, is what happens if you run out of time during your maintenance window? Previously, you would need to cancel your rebuild, wait for a potentially long recovery, then start again from scratch. This new feature gets rid of these problems! Cost of adoption: Change your index scripts to use the new RESUMABLE option. Pretty easy (another one!).

8. Data virtualization with Polybase

What this is: Polybase is SQL Server's module that allows fast and parallel T-SQL queries that can go out into external storage (usually HDFS on-prem) and return results seamlessly as a T-SQL result set. With SQL 2019, Polybase is getting expanded to support Oracle, Teradata, MongoDb and more. Why this matters: Data integration is always a challenge, and with ever-growing data sets, performance can become an issue. Trying to query and move large amounts of data through a linked server has always been painfully slow as it is not really optimized for this job. Polybase allows SQL Server to become the data hub of an organization by leveraging T-SQL skills and keeping performance as a top priority at the same time. Cost of adoption: This one definitely requires some work as you'd want multiple Polybase nodes, and would have to set up connectivity to your other database platforms, then test the performance of those queries. Data virtualization with Polybase.

9. Last actual execution plan DMF

What this is: This is a new Dynamic Management Function called sys.dm_exec_query_plan_stats that will track the last ACTUAL execution plan for a query if you enable the lightweight query profiling feature (which you probably should do). Why this matters: Previously, grabbing an actual query plan required either a Profiler trace, an XEvents trace, or a call to an ephemeral DMF that would lose its contents when the query was done executing. Not the easiest or most convenient mechanisms to do what is pretty much a critical step in any sort of production performance problem scenario. Cost of adoption: Enable the setting, use the DMF (another one to just use!).

10. Multiple internal performance improvements

What this is: The SQL team has made multiple internal performance improvements for this release. There's a new index optimization for indexes that have a sequential key, temp table recompilation improvements, improved indirect checkpoint scalability and more. Why this matters: These are all performance improvements that come "out-of-the-box," optimize common SQL Server processes and require no effort from the client to benefit from them. Cost of adoption: See line above. SCORE!

Parting Words

I hope you found this list interesting and that I've piqued your interest in SQL Server 2019. Once again, this is another evolutionary step in Microsoft's flagship database, and the story just continues to get better over time.

No Comments Yet

Let us know what you think

Share this