Exploring SQL Server 2019’s New Features: Part 1

Microsoft SQL Server 2019 introduced many new features and options that improve performance and add flexibility. In this post, we’re going to take a closer look at the setup options, processing options, and changes to TempDB. Next week we’ll take on more. Let’s go!

SQL Server 2019 Setup Options

Streamline your installation process by starting with the right parameters for every project. For example, you can set the minimum and maximum memory configurations during setup, which allows you to match it to your existing SQL instances and applications. You can also use this feature to control CPU allocations. Set the maximum degree of parallelism per query to accomplish this. Standardization between instances creates more consistency in your SQL Server 2019 deployments. It also creates an environment that’s easier to manage, tweak, and troubleshoot.

For Enterprise Server or CAL users, you now receive a warning if the server uses more than 20 physical cores. If you have Hyper-Threading enabled, this warning instead shows when more than 40 logical cores are found. You can switch the product key at this point in the installation or accept the limitations that are in-place.

SQL Server 2019 Processing Options

Intelligent Query Processing, also known as IQP, enhances the Query Optimizer in several ways. When you use this feature, you have higher quality queries along with a streamlined workload. Here are several ways this SQL Server 2019 feature helps your query processing:

• Adjust your grant sizes in batch and row mode operators to fix excessive grants. You can also use this feature to address insufficient memory problems.
• Expands batch mode execution to rowstore, when it previously only supported columnstore. This flexibility allows you to expand the workloads that you can use batch processing on, especially if you have a mix of rowstore and columnstore in use in your organization.
• Embed your scalar UDFs into SQL queries through an automatic transformation.
• Optimize your queries that use reference table variables through a compilation process.
• Reduce resources, boost concurrency, and improve the speed of queries that don’t need exact counts through Approximate query processing. When you avoid using Count for very large data sets, you can see significant improvements in the processing speed.

SQL Server 2019 TempDB Improvements

Heavy workloads create many bottlenecks that get in the way of productivity. SQL Server 2019 offers several features that enhance TempDB and related functions. The most significant improvement comes from the ability to move TempDB system tables into memory-optimized tables for an in-memory database. Instead of relying on disks that are struggling with the workload, you can easily use the TempDB transfer option to leverage memory. It’s a relatively simple process to set up, although it does require a server restart, so you’ll want to plan for that.

Microsoft got rid of Database Mirroring in 2012, but in 2019 you can now create database snapshots that include memory objects. You’re able to query these snapshots the same way you would your other databases.

Finally, a Hybrid Buffer Pool enables you to use clean data pages on persistent memory devices, rather than using the DRAM. You set this up through a memory-mapped I/O so the page isn’t saved to the DRAM. When you leverage this option, you can make better use of the infrastructure resources you have available.

These features are only a handful of the powerful options introduced in SQL Server 2019. Download our white paper, 7 New Features Strengthen SQL Server 2019 to learn more about the capabilities that Microsoft added to this system. If you looking to upgrade to 2019, contact us.