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.
View original post 603 more words