SQL SERVER – Number of Tables Memory Optimized in TempDB in SQL Server 2019

3 years ago

If you have learned about the new features of SQL Server 2019, you might already know about this feature called “memory-optimized tempdb metadata”. In this blog, I am not going to talk about the feature in-depth but would answer quick questions that I have heard from my clients. Let us learn about Memory Optimized in TempDB, which I had earlier discussed with my client Comprehensive Database Performance Health Check.

How to verify if it is enabled?
How to enable it?
How many tables are memory-optimized?
How to disable it?

Here are the answers with relevant scripts.

How to Verify if Memory Optimized in TempDB Enabled?

We can run queries in SQL Server to know if the feature is enabled.

Query # 1

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

If we get the output as zero, then the feature is disabled else enabled.

Query # 2

SELECT description, value_in_use,*
FROM sys.configurations
WHERE name LIKE 'tempdb metadata memory-optimized'

If value_in_user output as zero, then the feature is disabled. If it’s one, then enabled.

How to Enable it?

To enable this feature, you need to have SQL Server 2019 or above. There are two different methods to enable this feature.

Query # 1

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Query # 2

EXEC sp_configure 'tempdb metadata memory-optimized', 1
GO
RECONFIGURE
GO

How to Disable it?

If you don’t like this and want to disable it, here are the methods.

Query # 1

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF
GO

Query # 2

EXEC sp_configure 'tempdb metadata memory-optimized', 0
GO
RECONFIGURE
GO

The tempdb metadata configuration has been set to ‘disable’. Restart the SQL server for the new setting to take effect.

How Many Tables are Memory Optimized?

Here is the query to find the answer.

SELECT object_name(object_id) as Table_Name,
[row_insert_attempts], [row_update_attempts],[row_delete_attempts]
FROM [tempdb].[sys].[dm_db_xtp_object_stats]

As we can see there are around ten metadata tables that are qualified for optimization in memory. If we can ERRORLOG, we can see the below messages.

Starting up database ‘tempdb’.
[INFO] HkHostDbCtxt::Initialize(): Database ID: [2] ‘tempdb’. XTP Engine version is 2.11.
Tempdb started with memory-optimized metadata.

Hope this helps in answering basics deployment questions related to this feature. Let us connect via twitter and continue our conversation.

Reference: Pinal Dave (https://blog.sqlauthority.com)