With the release of SQL Server 2019 comes many new and exciting features, we can’t wait to share the ones that made our top five list! Overall there were vast improvements in the database engine, security, database recovery, and several other areas.
TempDB bottlenecks got you down? Tired of long database recovery times because you’re waiting on that one super long-running transaction? Does your headache have crazy transaction log growth written all over it? Are you stuck with poor performing queries due to scalar UDFs? Do you get bored waiting for that distinct count to finish on that enormous billion-row table? SQL Server 2019 may just have the solutions you’ve been looking for!
1. Memory-optimized TempDB Metadata
Are you tired of bottlenecks caused by heavy workloads on TempDB? Help has finally arrived! You can now move the tempdb’s system tables into memory-optimized tables taking the pressure off your disks. The good news it’s really simple to enable and makes TempDB more scalable. The bad news is that it requires a service restart to take effect and there are some limitations. However, the good may outweigh the bad in this case.
2. Accelerated Database Recovery (ADR)
Database recovery can take a significant amount of time and be a struggle to complete on time due to active long-running transactions. Managing transaction log growth, so it doesn’t run rampant has also been known to cause many headaches. With SQL Server 2019, the log truncation process is much more aggressive to help keep it from ballooning out of control and taking up all your disk space. When enabled, ADR uses a Persisted Version Store for tracking changes that allow for rolling back long-running transactions instantaneously. It doesn’t matter how long the transaction has been running or how large they are either.
3. Intelligent Query Processing (IDQ) Improvements
Many performance improvements were made to the engine itself. Some of the more exciting ones include Scalar UDF inlining, which automagically turns your scalar UDFs into something more efficient and set-based. Batch Mode on Rowstore allows you to use batch mode execution, and you no longer have to use columnstore indexes. Table Variable Deferred Compilation results in the use of actual cardinality instead of an estimate during optimization, and the first time it’s compiled. Approximate Query Processing via the new APPROX_COUNT_DISTINCT function is for those times you need a quick row count estimate of unique values (excluding nulls) in a billion-something table and count(distinct) is just too darn slow.
4. Replication in Linux
SQL Server on Linux now supports replication, including Snapshot, Transactional, and even Merge. The instance can support any of the replication roles: Publisher, Distributor, Subscriber. You can even go wild and mix and match the operating systems involved! For example, the publisher and distributor can be on Windows or Linux, and the subscriptions can be on a mix of Windows and Linux instances as well.
5. Always Encrypted with Secure Enclaves
A secure enclave is a memory space on the server where data can be accessible unencrypted and gives you more control over your encrypted data. The engine now has classification built-in and a new T-SQL interface to help you classify your data to meet compliance standards. You can use also audit to find out who accessed your confidential data.
These are only a handful of all the new and exciting features now available in SQL Server 2019. Improvements were also made to Availability Groups, Query Store, Azure Data Studio, and, pssst, get this… they finally did it. They finally changed the not-very-helpful “String or binary data would be truncated error message” into a message that includes not only the table and column names but also the value that was truncated!
Leveraging the memory-optimized TempDB metadata feature will help with your TempDB bottlenecks. ADR will help you significantly reduce your database recovery times because you no longer have to wait on that one super long-running transaction and will help control crazy transaction log growth. Need to use scalar UDFs but were afraid of the performance hit? Now you can use them knowing they are now more efficient! Do you need a quick estimated distinct count on that enormous billion-row table? It’s no longer a problem! Need more control over your encrypted data? Always Encrypted with Secure Enclaves may be just the solution you’re looking for.