Select Page

Exploring SQL Server 2019’s New Features: Part 1

George Criaris | | December 29, 2020

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.

 

Read This Next

7 New Features Strengthen SQL Server 2019

Download our paper to learn more about SQL Server and its database capabilities, 7 new features worth mentioning for SQL Server 2019, and why you should make the upgrade.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database.

Megan Elphingstone | February 2, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS