SQL SERVER 2019 – Memory-Optimized TempDB and RESOURCE_SEMAPHORE Wait Type

The other day, I received the following image from a client with whom I worked on Comprehensive Database Performance Health Check. The image was really huge and looking complicated. After looking at the execution plan, I realized it was actually built for a very simple query. Today let us discuss Memory-Optimized TempDB and Performance.

Before we discuss any further, first take a look at the following execution plan. Once you look at the execution plan, guess the statement it is running.

The same image can be seen in a larger format by clicking here.

Now let us tell you the statement which is producing such an execution plan.

SELECT *
FROM tempdb.sys.tables

Yes, it is difficult to believe that such a simple one-line statement produces such a complicated execution plan. As I have worked with the client many times before I am familiar with their workload and kind of work they do. They are dealing with 1000s of smaller transactions every single second and each of them involving the TempDB. Every single transaction creates a table in TempDB and the table is dropped at the end of the transaction.

Here is the output of the select statement above if you have enabled SET STATISTICS IO ON.

Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘Workfile’. Scan count 0, logical reads 0
Table ‘sysschobjs’. Scan count 1, logical reads 43
Table ‘syssingleobjrefs’. Scan count 5, logical reads 10
Table ‘syspalnames’. Scan count 1, logical reads 2
Table ‘sysidxstats’. Scan count 1, logical reads 7
Table ‘syspalvalues’. Scan count 2, logical reads 4

Next, we looked at the execution plans and figured out that they were facing issues with RESOURCE_SEMAPHORE wait type.

RESOURCE_SEMAPHORE Wait Type in TempDB

There are many different ways to resolve the RESOURCE_SEMAPHORE wait types and I will write a detailed blog post in the future. However, one thing is for sure whenever you see this wait type, you are for sure going to face performance troubles.

This wait type suggests that you are facing issues with memory and not enough memory is available to run the future queries (and some of the queued queries). Adding more memory is good if it is possible but in the 99% of the business cases which I have encountered, it has been not a practical solution (and often not possible as well).

The client had done many tests and was for sure when they enable the transactions in TempDB they were facing this issue and whenever they stop those particular transactions, the wait type RESOURCE_SEMAPHORE disappeared.

The question in which the client asked me is there any way to optimize TempDB without adding more memory to it.

Of course, there is away.

Memory-Optimized TempDB

In SQL Server 2019, you can enable to allow Memory Optimization of Metadata in TempDB.

TempDB contains mainly two kinds of data:

• Metadata describing the data structures stored
• Data contained within data structures

Now, in SQL Server 2019, you can for sure optimize your TempDB for the memory optimization of the TempDB but not the data contained in the data structures like temporary tables, etc.

In simple words, most of the data in the system tables can be memory-optimized but not the temporary table which you created in the temp table. Honestly, it totally makes sense as well. TempDB is created every single time when you restart the service and also all the data stored in the TempDB is supposed to be volatile anyway.

Here is the command which you can run in SQL Server 2019 and make enable your Memory-Optimized TempDB for Metadata.

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

After you run the above command, restart your SQL Server services.

After the restart is complete, now let us run the same SELECT command which we had ran earlier ran.

Now when I compared the execution plan, honestly, I did not find much of the difference. However, in the STATISTICS IO, I was able to see some differences. Here is the output of it:

Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘Workfile’. Scan count 0, logical reads 0
Table ‘syspalnames’. Scan count 1, logical reads 2
Table ‘syspalvalues’. Scan count 2, logical reads 4

After enabling MEMORY_OPTIMIZED TEMPDB_METADATA, the customer started to get better performance as they had contention in the MetaData in TempDB. The RESOURCE_SEMAPHORE wait types were very much reduced leading to overall better performance for hte client.

Summary

SQL Server 2019 has new feature of Memory Optimized TempDB for MetaData. This feature will help the performance of your system if you have performance trouble due to metadata contention or heavy read-write on the TempDB (which leads to the metadata of the tempdb). If your workload does not qualify after enabling this feature, you will not get any performance improvement.

I usually discuss this in detail in my Comprehensive Database Performance Health Check. 

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