The Life of Dreier 393

cocoaounce97's blog

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

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.

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

Go Back


Blog Search

Blog Archive


There are currently no blog comments.