Prashanth Jayaram
tempdb output

SQL Server 2019 Memory-Optimized TempDB Metadata

July 16, 2020 by

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:

  1. Details of the in-memory-optimized TempDB metadata
  2. Prepare T-SQL samples to demonstrate In-memory optimized TempDB metadata
  3. Discuss how to enable memory-optimized TempDB metadata feature
  4. Explain the use of the OStress utility
  5. Implement workload using OStress commands
  6. Measure the performance and resource contention on tempdb catalog view
  7. Discuss the limitation
  8. 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:

Script 2:

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:

Now, the batch file is ready to execute.

Ostress test memory-optimized Ostress test

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.

Ostress test

Run the following SQL to check the contention on TempDB:

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.

tempdb output

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.

Ostress execution time

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:

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

  2. 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:

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

    sysconfiguration output

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

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

    Memory optimized tempdb check

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

    sysconfiguration output

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.

OStress parameters

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.

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

Contention free snippet

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.

OStress execution time

To disable MEMORY_OPTIMIZED TEMPDB_METADATA run the following T-SQL:

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…

Prashanth Jayaram
SQL Server 2019

About 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

977 Views