Scalability

Article
02/28/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server 2016 (13.x) contains scalability enhancements to the on-disk storage for memory-optimized tables.

Multiple threads to persist memory-optimized tables

SQL Server 2014 (12.x) had a single offline checkpoint thread that scanned the transaction log for changes to memory-optimized tables and persisted them in checkpoint files (such as data and delta files). In machines with a larger number of cores, the single offline checkpoint thread could fall behind.

Starting with SQL Server 2016 (13.x), there are multiple concurrent threads responsible to persist changes to memory-optimized tables.

Multi-threaded recovery

In the previous release of SQL Server, the log apply as part of recovery operation was single threaded. Starting with SQL Server 2016 (13.x), the log apply is multi-threaded.

MERGE Operation

The MERGE operation is now multi-threaded.

Note

Manual Merge has been disabled as multi-threaded merge is expected to keep up with the load.

Dynamic management views

The DMVs sys.dm_db_xtp_checkpoint_stats (Transact-SQL) and sys.dm_db_xtp_checkpoint_files (Transact-SQL) have been changed significantly.

Storage management

The In-memory OLTP engine continues to use memory-optimized filegroup based on FILESTREAM, but the individual files in the filegroup are decoupled from FILESTREAM. These files are fully managed (such as for create, drop, and garbage collection) by the In-Memory OLTP engine.

Note

DBCC SHRINKFILE (Transact-SQL) is not supported.