Esat Erkec
Eliminating the PAGELATCH_UP latch problem fort he TempDB database

SQL Server TempDB database and Latch Contention

May 13, 2020 by

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.

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.

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.

Using SQLQueryStress for SQL Server TempDB  database contention

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

SQLQueryStress database settings

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

Starting SQLQueryStress

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

Analysing SQL Server TempDB Database Metadata Contention  with sp_whoisactive

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:

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

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

Enabling Memory-Optimized TempDB Metadata feature for SQL Server 2019

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:

List of the SQL Server TempDB table which was converted to the memory-optimized

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.

Eliminating the PAGELATCH_EX for the TempDB database

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

    Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled.

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

    Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled.

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:

  • We will set the Number of Iterations as 100 and set Number of Threads as 200:

    Using SQLQueryStress for TempDB  database contention

  • We will click the Database button and set the database credentials and other settings:

    Database settings of the SQLQueryStress

  • We will click the GO button to start executing the query:

    Starting  SQLQueryStress to execute the queries

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

Eliminating the PAGELATCH_UP latch problem fort he TempDB database

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.

Esat Erkec
357 Views