In-memory TempDB metadata feature
New feature in SQL 2019 to address metadata contention
In SQL 2019, there is a new feature introduced 'in-memory TempDB metadata feature' to address metadata contention in TempDB. When you see PAGELATCHes on system object tables, then you know it is metadata contention. Read here to understand metadata contention in detail.
What is the feature about:
This is a new feature introduced in SQL 2019 as part of in memory OLTP umbrella.
When this feature is enabled, it moves all the heavily utilized system objects to memory, thus making them latch free and reducing contention.
This feature needs to be manually turned ON and it is OFF by default.
SQL services need to be restarted for it to take effect.
Make sure you are on a minimum of CU2 on SQL 2019 feature, since only from SQL 2019 CU2 all 12 metadata tables are moved to memory.
This does not need a separate filegroup to be created.
This feature has nothing to do with contention on PFS, GAM or SGAM pages.
This only helps with contention on system object tables due to DDL operations.
Enable the feature and test it:
On SSMS, run below query to check if the property is ON/OFF.
Value 0 indicates it is OFF
/*VERIFY MEMORY-OPTIMIZED TEMPDB METADATA FEATURE*/
/*NOT ON BY DEFAULT*/
SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;
GO
Let us run a workload against the SQL Server and see if it has PAGELATCHes
While the workload is running, check for contention in SSMS. There seem to be PAGELATCHes on sysschobjs , which is nothing but metadata contention.
Now let’s turn on the feature and check if that resolves the issue.
Run ALTER CONFIGURATION to turn the feature ON:
WARNING: DO NOT DO THIS ON PRODUCTION
Test it on a development or sandbox and then implement on production. Discuss and review it internally with the team and then perform this during maintenance window with a change request after thorough testing.
/*ENABLE MEMORY-OPTIMIZED TEMPDB METADATA */
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;
GO
Now, SQL service needs to be restarted for the feature to take effect.
When you restart, on a standalone instance, restart the service from SQL Server Configuration Manager. On clustered nodes, restart the service from Failover Cluster Manager.
Make sure this feature is enabled on both active and passive nodes or on both primary and secondary nodes, so when the failover takes place, the feature is enabled on all the nodes and is consistent throughout.
After restarting, check to make sure the feature is enabled. Value 1 indicates the feature is turned ON.
SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;
GO
As this feature is ON, we can see 12 metadata tables now moved to memory. You can verify this using the following query:
/*CHECK WHICH SYSTEM TABLES HAVE BEEN CONVERTED TO MEMORY-OPTIMIZED*/
USE TEMPDB
GO
SELECT t.[object_id], t.name
FROM tempdb.sys.all_objects AS t
INNER JOIN tempdb.sys.memory_optimized_tables_internal_attributes AS i
ON t.[object_id] = i.[object_id];
This will get rid of PAGELATCHes on system object pages as they are moved to memory and helps in addressing metadata contention.
Now, let’s run the same workload again and check if there are any PAGELATCHes
Check for contention in SSMS and observe that there are no latches waiting on system object pages.
So we observed that turning on in-memory tempdb metadata feature helps to resolve metadata contention on TempDB in SQL Server.
Downside:
Though this feature works on both SQL 2019 and 2022, I've seen unintended SQL service restarts due to out of memory errors on SQL 2019. Resource pool can be set up but when it needs more memory than what is set up, it ends up restarting the SQL service. So I recommend not enabling it on SQL 2019, instead enable it on SQL 2022 where it has been performing reliably.
Before you go ahead and enable this feature, it is important to address this contention by reviewing the queries, so as to not drop temporary objects and cache the temporary objects wherever possible. This by itself mitigates a lot of contention and after this is done and if you still notice that there are PAGELATCHes, then enable this feature. Here is the link to read more about why temporary objects shouldn’t be dropped and how/why to cache temporary objects
Summary:
In SQL 2019, there is a new feature introduced 'in-memory TempDB metadata feature' to address metadata contention in TempDB. This feature is OFF by default and needs SQL services to be restarted after it is turned ON. When it is enabled, it moves all the heavily utilized system objects to memory, thus making them latch free and reducing metadata contention. This helps reducing contention only on system object pages and not PFS, GAM or SGAM pages.
Resources:
https://www.mssqltips.com/sqlservertip/6230/memoryoptimized-tempdb-metadata-in-sql-server-2019
TempDB: The Good, The Bad, and The Ugly by Pam Lahoud
https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my
Thanks for reading,
Haripriya