por André Batista / Project Line Manager

Source: Microsoft

SQL Server 2019 was released last November and there are several features that Microsoft implemented on its Data Platform that will transform this ecosystem providing to the customers more reliability, performance, resiliency, and robustness.

Main new features:

1 – Accelerated Database Recovery (ADR)

This is a completely new way for SQL Server to perform a database recovery in the event of a transaction being rolled back, an instance restart or an Availability Group failure. Because of the often volatile time spent waiting for the database recovery to run, the SQL team has re-developed how recovery works and significantly reduces how long this cycle takes.

2 – Intelligent Query Processing Enhancements

The actions and optimizations of the Query Optimizer is the component in the SQL Server that produces database execution plans. It involves customizable resource grants for rowstore tables, table vector deferred compilation, batch mode on rowstore, and a lot more tweaks that the SQL Server engineering team has made to the product.

3 – Resumable Index Build

SQL Server also provides the power to interrupt an online index rebuild process, to catch up with the rebuild that has been completed so far, and to restart at any other point of time. Index rebuilds are still required and take so many resources that even with the ONLINE alternative, they still have to deal most of the time inside of the database maintenance windows.

4 – New DMF to troubleshoot LAST execution plan

There’s a new Dynamic Management Function (DMF) called sys.dm_exec_query_plan_stats that will store the last ACTUAL execution plan for a query. Before the methods that were available, mostly via XEvents or a SQL Trace were costly and not the most convenient method. SQL Server 2019 helps to troubleshoot more easily the LAST execution plan for a specific executed query.

5 – Improved TSQL warning\error messages

How many times is it that after running a script with thousands of instructions, we get a TRUNCATE error in which we don’t know which record is having problems? By default and without having to activate anything, SQL Server 2019 finally identifies the registry with problems so that we can resolve it in seconds, which often took minutes. Thank you, Microsoft !!!!!

Problems with Cumulative Update 2 (Build 15.0.4013.40)

Warning: If you pretend to install a new SQL Server 2019 instance with the last cumulative update (CU2) be careful since there are several users reporting problems with SQL Agent stopped working.

Known issue: