SQL Server 2019 Memory-Optimized TempDB Metadata

In this article, I will walk you through the new feature in SQL Server 2019, memory-optimized TempDB metadata. The most commonly faced performance problems in SQL Server world is known to be TempDB resource contention. Don’t you agree? Let us find the answer in this article.

This is the continuation of the previous articles: A quick overview of In-Memory OLTP in SQL Server, How to monitor internal data structures of SQL Server In-Memory database objects and SQL Server In-Memory database internal memory structure monitoring.

This article is an effort to detail the concepts of In-memory-optimized TempDB.

In this article, we will cover:

Details of the in-memory-optimized TempDB metadata
Prepare T-SQL samples to demonstrate In-memory optimized TempDB metadata
Discuss how to enable memory-optimized TempDB metadata feature
Explain the use of the OStress utility
Implement workload using OStress commands
Measure the performance and resource contention on tempdb catalog view
Discuss the limitation
And more…

Introduction

In-memory optimized technique leveraged its footprint to one of the most and much-waited resource contention management of the TempDB database. The memory-optimized TempDB metadata contention is a solution to many heavy workloads where we used to see a bottleneck to scaling heavy workloads running on older SQL Server versions. SQL Server 2019 introduces a new feature “memory-optimized TempDB metadata” in the umbrella of the In-Memory optimized Database feature set. It greatly simplifies and effectively manages the resource contention and unlocks greater flexibility to handle and scale heavy TempDB workloads.

Get started

This article is explained in two phases. First, I will walk through the normal scenario on how the TempDB works—as we all know, the most common performance issue in SQL Server is considered to be as TempDB contention. We will simulate a scenario where I will create a temp table, and we will see how the temp table is created each time and how the SQL Server builds an internal pressure to maintain the resource allocation along with managing the metadata.

Second, I will walk through the new feature memory-optimized TempDB to demonstrate how the system tables are maintained in managing temporary table metadata and see how it is moved into latch-free non-durable tables.

Preparing T-SQL script

In this section, we will see how to create a nested script, how to perform a stress test using OSTRESS, how to find the page latch details, how to measure the contention.

It’s a simple script to create contention and workload on the TempDB database. You can see the #dummytable is created in each call with a row inserted from the sys.sysobjects system view. You can refer to an article from Nikilesh Patel on how to stress SQL Server using OStress utility.

T-SQL Script

Launch SSMS and open a new query pane, then run the following T-SQL script to create two procedures.

Script 1:

CREATE OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_1 AS

BEGIN

SET

NOCOUNT

ON;

CREATE TABLE #DummyTable ( ID BIGINT NOT NULL );

INSERT INTO

#DummyTable

SELECT

T.RowNum

FROM

(

SELECT

TOP (1) ROW_NUMBER() OVER (

ORDER BY

(

SELECT

NULL)) RowNum

FROM

sys.sysobjects

)

END

Script 2:

CREATE

OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_2 AS

BEGIN

DECLARE @id int = 0;

WHILE( @id < 10 )

BEGIN

EXECUTE dbo.SPROC_tempdbMemOptimzedtest_1;

SET

@id = @id + 1;

END

OStress utility

OStress is a free Microsoft bundled with an RML utility package. The tool scalable and command-line tool which can be used to stress or replay T-SQL queries on the database. It lists a wide range of switches that can be used to simulate heaving workload by running the script with multiple iterations

Let us load the OStress utility to simulate the stress on the TempDB database. In this section, we will see how to create a batch file with the OStress commands. This way, we can keep the call very simple, and it is easy to make changes to the batch file.

In this example, create a stresstest.bat file with the following content:

1 2	cd C:\Program Files\Microsoft Corporation\RMLUtils\ ostress.exe -Slocalhost -E -dMongoDB -Q'EXECUTE dbo.SPROC_tempdbMemOptimzedtest_2;' –n100 –r30

Now, the batch file is ready to execute.

The parameters used for ostress.exe, ‘-S’ is the server (in this case, it’s localhost); ‘–d’ is for the database—MongoDB is the database where we created the stored procedure and ‘–Q ‘is for running the standalone SQL queries—in this case, recurrence stored procedure executed with 100 sessions with the concurrent executions of 30 iterations.

Let us execute the batch file and measure the workload.

Run the following SQL to check the contention on TempDB:

SELECT

req.session_id,

req.wait_type,

req.wait_resource,

OBJECT_NAME(inf.[object_id], inf.database_id) as [object_name],

req.blocking_session_id,

req.command,

SUBSTRING(txt.text,

(

req.statement_start_offset / 2

)

+ 1,

(

CASE

req.statement_end_offset

WHEN

- 1

THEN

DATALENGTH(txt.text)

ELSE

req.statement_end_offset

END

- req.statement_start_offset) / 2

)

+ 1) AS statement_text, inf.database_id, inf.[file_id], inf.page_id, inf.[object_id], inf.index_id, inf.page_type_desc

FROM

sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt

CROSS APPLY sys.fn_PageResCracker (req.page_resource) AS pgc

CROSS APPLY sys.dm_db_page_info(pgc.[db_id], pgc.[file_id], pgc.page_id, 'DETAILED') AS inf

WHERE

req.wait_type like '%page%'

In the following output you can see the wait_type ‘PAGELATCH_EX’ and you can also see the EXECUTE recursive stored procedure on TempDB database.

In the following snippet, you can see that the recursive procedure call is executed with a simulation of 100 user connections with 30 concurrent execution took 67 seconds.

In this section, we will study and understand the implication of memory-optimized TempDB metadata.

Enabling memory-optimized TempDB metadata

To enable the TempDB feature in SQL Server, two steps are required, and the third one is optional:

To alter the server configuration, run the following T-SQL:

1

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;

Restart SQL Service in order to take part in the changes to reflect on the SQL Server instance

You can also use sp_configure to set the memory-optimized TempDB metadata parameter using the following T-SQL:

EXEC sys.sp_configure N'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

EXEC sys.sp_configure N'tempdb metadata memory-optimized', 1;

RECONFIGURE WITH OVERRIDE;

In the following snippet, you can see that the TempDB metadata memory-optimized parameter is already enabled.

To validate the server configuration run the following T-SQL:

1

SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;

The output “1” indicates that the memory-optimized TempDB is enabled on the SQL Server instance.

You can also check the configuration using sp_configure and sys.configuration system views:

1
2
3

EXEC sp_configure 'tempdb metadata memory-optimized'

select * From sys.configurations where name like '%tempdb%'

Let us stress the SQL Server using the OStress utility. In this section, you can see the heavy workload applied using OStress commands.

In this case, the same aforementioned OStress command utility is run (the batch file is re-executed), and output is captured in the below snippet.

The below T-SQL script is used to find queries that are currently running on the SQL Server instance. In the T-SQL, you also see that the use of a new dynamic managed function used to view the page allocation and page resource wait_types details.

SELECT

req.session_id,

req.wait_type,

req.wait_resource,

OBJECT_NAME(inf.[object_id], inf.database_id) as [object_name],

req.blocking_session_id,

req.command,

SUBSTRING(txt.text,

(

req.statement_start_offset / 2

)

+ 1,

(

CASE

req.statement_end_offset

WHEN

- 1

THEN

DATALENGTH(txt.text)

ELSE

req.statement_end_offset

END

- req.statement_start_offset) / 2

)

+ 1) AS statement_text, inf.database_id, inf.[file_id], inf.page_id, inf.[object_id], inf.index_id, inf.page_type_desc

FROM

sys.dm_exec_requests AS req

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt

CROSS APPLY sys.fn_PageResCracker (req.page_resource) AS pgc

CROSS APPLY sys.dm_db_page_info(pgc.[db_id], pgc.[file_id], pgc.page_id, 'DETAILED') AS inf

WHERE

req.wait_type like '%page%'

In the following snippet, we can see that there is no contention in managing the TempDB tables.

In the following snippet, you see that the SP execution with a 100-user connection with 30 concurrent executions took just 31 seconds to complete.

To disable MEMORY_OPTIMIZED TEMPDB_METADATA run the following T-SQL:

1	ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=OFF;

For the change to take effect, you need to restart the SQL Service.

Conclusion

In this article, we have discussed the new memory-optimized TempDB metadata. Furthermore, we saw how the system catalog is involved in managing latch free and non-durable temp table metadata. SQL Server 2019 introduced another scalability feature along with in-memory optimized techniques. TempDB metadata contention has been a bottleneck for a long time. The traditional way of managing the tempdb meta-data system tables is moved into the use of latch-free non-durable memory-optimized tables.

With this scalable option, SQL Server can handle tempdb workloads in a much better way. Hopefully, you also understand the limitation to enable this feature — to switch the memory-optimized metadata on-and-off requires a SQL Service restart.

The SQL query running on memory-optimized tables does not support SQL hints. The queries run against the TempDB metadata catalog views will not work when you use lock and isolation SQL hints.

The other limitation of the memory-optimized tempdb metadata table is isolation. It means that a single transaction can not access the memory-optimized tables in more than one database. It is self-contained within the transaction.

In case, If you access the TempDB system views in the same the transaction, you will receive the following error:

“A user transaction that accesses memory-optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master”

I hope you enjoyed reading this article. Please leave the comment below.

That’s all for now…

Author
Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

Introduction

Get started

Preparing T-SQL script

T-SQL Script

Script 1:

Script 2:

OStress utility

Enabling memory-optimized TempDB metadata

Conclusion

Related posts: