SQL Server

One of the most important features in SQL Server 2019 is Memory-Optimized TempDB Metadata. This is a server feature, by turning it on, you can significantly improve tempdb performance by eliminating page latch contention for tempdb-heavy workloads. You can learn about it, and see the feature in action in two excellent videos from the SQL Server 2019 Learning Series: https://www.youtube.com/watch?v=LQejtjKERWM and https://www.youtube.com/watch?v=g4aemv5O9as. In this post, I am gonna write about the feature’s limitations.

When we are about to enable a new feature, one of the first things we have to check is whether enabling this feature will break the existing code. Enabling this feature could bring two breaking changes: one is related to columnstore indexes, the other to transactions with memory-optimized tables. Before we start to create and invoke database objects, we have to ensure that the feature is turned on. This query should return 1.

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

View original post 603 more words