Transcript

Benefits of SQL Server 2017 and 2019

Batch mode execution: SQL Server 2019 uses only one parallelism zone for less processing. Version 2019 also uses columnstore indexes for smaller query results since queries now only store unique values.

Table-valued function interleaved execution: With interleaved execution, SQL Server 2017 runs multi-statement table-values functions first and then passes the row estimates to the rest of the plan, for improved memory grant accuracy.

Adaptive memory grants: Adaptive memory grants can reduce the number of pages spilled to TempDB as SQL Server reviews its memory use after a query, although results can still be inconsistent.

Scalar function inlining: SQL Server 2019 automatically rewrites row-based scalar user-defined functions into set-based functions (and also rewrites the related queries). Microsoft is still debugging this new feature.

Adaptive joins: Adaptive joins for columnstore indexes in SQL Server 2017 expanded to rowstore indexes for 2019. Both require SELECT queries with joins that can work with nested loops or hash joins.

Automatic tuning: Automatic tuning stores multiple query plans with Query Store to measure their performance to determine which one is better.

Microsoft is doing well with its latest versions of SQL Server, which can reduce its hosting costs for Azure if done right. This objective is crucial for making Azure more cost competitive with Amazon AWS and other on-premises database platforms.