SQL Server TempDB database and Latch Contention

In this article, we will learn latch contention issues that we might experience in the SQL Server TempDB database. We will also discuss reasons and the solution method of these latch contention issues. Especially, we will mention the Memory-Optimized TempDB Metadata feature that was introduced with the SQL Server 2019.

Firstly, we will briefly learn the essential characteristics of the TempDB database, and we will also talk about the latch concept of the SQL Server so that we can understand all aspects of the latch contention problems of the TempDB database more clearly.

What is the TempDB database used for in SQL Server?

TempDB database is one of the system databases of the SQL Server, but it has various unique functionalities as distinct from other system databases. Global and local temporary tables are created in this SQL Server TempDB database, and the data of these tables are stored by this database. At the same time, table variables, temporary stored procedures, and cursors are used in this database resource. In addition, TempdDB resources are also used by the following features.

Snapshot Isolation and Read-Committed Snapshot Isolation
Online index operations
MARS – (Multiple Active Result Sets)

When we restart the SQL engine TempdDB database is dropped and re-created. We can not take back up this database and can not change the recovery model from simple to others. When we are taking into account all of these, we can say that the TempDB database settings directly affect query performances.

What is the latch in SQL Server?

A SQL buffer pool is the memory place that is reserved by the operating system for the SQL Server, and it is also called as SQL buffer cache. SQL Server transfers the data pages into the memory from the disk in order to read or manipulate them and sends them back to disk according to a special logic. The main purpose of this mechanism is the desire to deliver faster performance to clients because memory is always faster than the storage systems. In this context, we need a mechanism to guarantee the data pages consistency in the buffer pool. A latch is a synchronization object used to protect data structures held in memory against inconsistency and corruption so that SQL Server ensures the consistency of data pages in the memory. This synchronization operation is managed by the SQL Server internally.

TempDB database Metadata Contention

TempDB metadata contention occurs when many sessions try to access the SQL Server TempDB’s system tables at the same time during the creation of the temp tables. This heavy workload causes latency on these system tables due to this reason, and the query performance will be decreased.

Now, we will create a fake workload on the TempDB to simulate this problem. We will use an oldie but goodie tool named SQLQueryStress to generate a fake workload on the TempDB database.

At first, we will create the following procedure. This stored procedure will create a temp table and will insert random 20 rows from the sys.all_columns table.

CREATE PROC ProcTest

BEGIN

CREATE TABLE #t1

(

c1 INT,

c2 INT);

INSERT INTO #t1

SELECT TOP 20 column_id,

system_type_id

FROM sys.all_columns WITH(NOLOCK);

END

We will launch the SQLQueryStress and paste the following query into the query panel. This query executes the ProcTest stored procedure 100 times in a WHILE loop.

DECLARE @i INT;

SET @i = 1;

WHILE @i <= 100

BEGIN

EXEC ProcTest;

SET @i = @i + 1;

END

We will set the Number of Iterations as 100 and will set Number of Threads as 25 so that the stored procedure executed 2500 times.

We will click the Database button and set the database connection and credentials settings.

We will click the GO button to start executing the query.

While SQLQueryStress is performing the query, we are executing sp_WhoisActive and analyze the results.

As we can see, the PAGELATCH_EX wait type can be seen in the wait_info column for the TempDB database. Specific to the TempDB database, we can overcome this wait using a new feature of the SQL Server 2019. In the next section, we will learn this feature.

Memory-Optimized TempDB Metadata

When we enable the Memory-Optimized TempDB Metadata feature, it converts some of the SQL Server TempDB system tables to non-durable memory-optimized tables, so it minimizes the latency on TempDB’s system tables. Memory-optimized tables offer low latency, high throughput, and accelerated response time, so this feature takes advantage of these performance enhancements.

We can enable this feature through the following query:

1	ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Or we can use the following query to enable this option.

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

RECONFIGURE;

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

RECONFIGURE;

The following query helps us to detect the status of this feature.

SELECT

CASE SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

WHEN 1 THEN 'Enable'

WHEN 0 THEN 'Disable'

END

AS 'Memory-Optimized TempDB Metadata Status'

After enabling the memory-optimized TempDB metadata feature, the SQL Server engine must be restarted. After restarting the SQL Server, we can see the list of the tables that are converted to the memory-optimized tables through the following query:

SELECT mem_table.[object_id], obj.name

FROM tempdb.sys.all_objects AS obj

INNER JOIN tempdb.sys.memory_optimized_tables_internal_attributes AS mem_table

ON obj.[object_id] = mem_table.[object_id]

When we rerun the SQLQueryStress for the same query with the same parameters. The sp_WhoIsActive output will be changed, and we don’t see any PAGELATCH_EX wait type.

Memory-Optimized TempDB Metadata feature has some limitations, and we should consider these limitations before deciding to use it:

The column store indexes can not be created for the temp tables when Memory-Optimized TempDB Metadata is enabled

1
2

CREATE TABLE #temp1 (val1 INT, val2 NVARCHAR(50));
CREATE COLUMNSTORE INDEX indexcol1 ON #temp1(val2);

At first, we created a local temporary table that name is #temp1, and when we tried to create a columnstore index to it, we could not succeed because the memory-optimized metadata feature is enabled.

sp_estimate_data_compression_savings built-in procedure does not run for the tables that include columnstore indexes when Memory-Optimized TempDB Metadata is enabled

CREATE TABLE t1 (val1 INT, val2 NVARCHAR(50));

CREATE COLUMNSTORE INDEX indexcol1 ON t1(val2);

EXEC sp_estimate_data_compression_savings 'dbo', 't1', NULL, NULL, 'ROW' ;

Stored Procedure, sp_estimate_data_compression_savings calculates the estimated compression gains for the tables before to compress operation. However, when we enabled the Memory-Optimized TempDB Metadata option, this procedure does not work for the t1 table because it includes a columnstore index.

TempDB Database Allocation Page Contention

Data pages are the fundamental unit of the SQL Server that stores data, and the size of the data pages are 8 KB. The eight physically contiguous data pages are named extent. Information about which extents are allocated is recorded by the Global Allocation Map (GAM). Information about which extents are used as mixed is recorded by the Shared Global Allocation Map (SGAM). Page Free Space (PFS) records how much free space is available on which page in the extents.

A session should update the SQL Server TempDB allocation pages when creating and dropping temporary tables. As this number of concurrent connections begins to increase, accessing these pages allocation will become more difficult because, at a time, only one thread is able to change these pages, so other threads have to wait for this page to be released allocated resource. Now we will simulate this scenario.

We will launch the SQLQueryStress and paste the following query into the query panel:

1
2
3

SELECT TOP 2500 *
INTO #t1
FROM sys.all_objects WITH(NOLOCK);
We will set the Number of Iterations as 100 and set Number of Threads as 200:
We will click the Database button and set the database credentials and other settings:
We will click the GO button to start executing the query:

While SQLQueryStress is performing the queries, we are executing sp_WhoisActive and analyze the result of the wait_info column.

1	EXEC sp_WhoIsActive

As we can see, the PAGELATCH_UP wait type can be seen in the wait_info column. If we add more data files to the TempDb database, this problem will be minimized, and Microsoft recommends a formula for how many files we need.

“If the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors. If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels.”

According to this formula, we can increase the file number of the TempDB database to minimize this problem.

Conclusion

SQL Server TempDB database settings affect the performance of the queries, so we have to configure it attentively. In this article, we discussed the latch contention issues that we might face in the TempDB database and also walked through the corresponding solution methods.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec