DB-유

What's New in SQL Server 2019 for DBAs

· mssql, sql-server-2019, upgrade

SQL Server 2019 went generally available in November. After a few weeks on test systems I've stopped reading the marketing list and started keeping my own. Big Data Clusters and Java got the launch attention, but the features below are the ones that change how I troubleshoot and tune day to day, each with a quick way to try it.

Most need compatibility level 150, so start there.

Compatibility level and edition

SELECT @@VERSION;
SELECT name, compatibility_level FROM sys.databases WHERE database_id > 4;
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 150;

Version 15.0.2000.5 is the RTM build. Note that Standard Edition now has almost the same feature set as Enterprise for the items below, including Accelerated Database Recovery and the Intelligent Query Processing family.

Accelerated Database Recovery

Long rollbacks and long crash recovery after an unexpected restart have always been proportional to the size of the transaction. ADR changes the recovery model internally: a persistent version store in the user database makes rollback nearly instant and truncation of the log no longer waits on the oldest active transaction.

ALTER DATABASE Sales SET ACCELERATED_DATABASE_RECOVERY = ON;

The database needs no active transactions when you turn it on. The cost is extra space in the user database for the version store; watch sys.dm_tran_persistent_version_store_stats. For databases with occasional huge transactions or where log growth due to ACTIVE_TRANSACTION has been a recurring problem, this is the feature I'd upgrade for.

Intelligent Query Processing

Several optimizer improvements switch on automatically at compatibility level 150:

  • Table variable deferred compilation: table variables are no longer estimated at one row; the actual row count at first execution is used. Procedures that stuff a table variable with thousands of rows and then join it often get a sane plan for the first time.
  • Scalar UDF inlining: scalar user-defined functions that meet the criteria are inlined into the calling query instead of executed row by row. Check is_inlineable in sys.sql_modules to see which of yours qualify.
  • Batch mode on rowstore: analytical queries over large tables can use batch mode execution without needing a columnstore index.
  • Row mode memory grant feedback: a query that spilled or over-requested memory has its grant adjusted on the next execution.

You can turn each off individually if one causes a regression, without dropping the compatibility level:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

Better troubleshooting information

Lightweight query profiling is now on by default, which means you can see the actual plan of a currently running query with:

SELECT r.session_id, qps.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_query_plan_stats(r.plan_handle) qps
WHERE r.session_id = 75;

After a query finishes, the last actual plan is available for the statements in the cache when you enable LAST_QUERY_PLAN_STATS:

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

And the truncation error message finally tells you what was truncated. Instead of String or binary data would be truncated, you get:

Msg 2628, Level 16, State 1
String or binary data would be truncated in table 'Sales.dbo.Customers', column 'Email'. Truncated value: 'someone.with.a.very.long.address@ex'.

This is the default at compatibility level 150 and is also available in 2017 CU12 and later with VERBOSE_TRUNCATION_WARNINGS.

tempdb metadata contention

Systems that create and drop many temporary tables per second used to stall on latch contention in tempdb system tables. 2019 can move that metadata into memory-optimized tables:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

A restart is required. Check it took effect with SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized'). There are limitations (a single transaction cannot touch memory-optimized tables in a user database and tempdb system views at the same time), so test before enabling in production.

Smaller items worth knowing

  • UTF-8 collations: Latin1_General_100_CI_AS_SC_UTF8 and friends let varchar columns store UTF-8, potentially halving storage for mostly-ASCII text compared with nvarchar.
  • Certificate management in Configuration Manager: certificates for TLS can now be installed to all nodes of a cluster or AG from the GUI.
  • Always Encrypted with secure enclaves: range comparisons and LIKE on encrypted columns, given a VBS enclave on Windows Server 2019.
  • Linux: replication, Distributed Transaction Coordinator, and Change Data Capture work on the Linux build now, and the container images run as non-root by default.
  • Resumable online index create: CREATE INDEX ... WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES) pauses and continues, complementing the resumable rebuild from 2017.
  • sys.dm_db_page_info: page header information without DBCC PAGE.

Watching for regressions

After raising the compatibility level I leave Query Store on and look for regressions for a week or two, same as for any other level change:

SELECT TOP 20 q.query_id, qt.query_sql_text, rs.avg_duration / 1000 AS avg_ms, rs.last_execution_time
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
ORDER BY rs.avg_duration DESC;

Notes

  • ALTER DATABASE ... SET ACCELERATED_DATABASE_RECOVERY fails with Msg 5011 if there are open transactions. Retry in a quiet window.
  • A UDF is not inlined even at level 150: check sys.sql_modules.is_inlineable and inline_type. Functions using GETDATE(), table variables, or EXECUTE AS are excluded.
  • Compatibility level 150 changes plans. Keep Query Store enabled before the switch and be prepared to force a plan or turn off an individual IQP feature rather than dropping the level.
  • Not every new item is in every edition. Big Data Clusters need Enterprise; secure enclaves need specific hardware. Check the editions comparison before promising a feature.

If I had to pick three: ADR, live actual plans, and the truncation message that finally names the column. I'd upgrade for those alone.