Optimize SQL Server TempDB

Problem

TempDB is the unsung hero of SQL Server that is silently assisting temporary objects, large sorting, hash joining, and session-specific tasks. Understanding how it works and how to optimize it plays a crucial role for maintaining a high performing production environment.

Solution

We will examine what TempDB is, monitor its size, stress test it, and review some best practices for optimizing its performance. Let’s start.

SQL Server TempDB FAQs

Contention in TempDB

TempDB data files are global resource and working spaces used by both user and SQL Server’s internal objects. Three special pages (1) Global Allocation Map (GAM), (2) Shared Global Allocation Map (SGAM), and (3) Page Free Space (PFS) are responsible for allocating these working spaces.

If a task wants to access this resource, SQL Server lock manager places an appropriate latch on these pages to ensure serializing access. Many concurrent requests can quickly queue up resulting in “contention”.

Common contentions are:

• Object Allocation Contention – This is contention for metadata pages those manage space allocation.
• Metadata Contention – This is contention for metadata pages tracking the creation and destruction of temporary tables and deallocating them.
• Temp Table Cache Contention – Temporary table cache helps metadata and object allocation for reusing of temp tables.

Simulate TempDB Contention and Memory Utilization

Let’s use SQLQueryStress to generate stress on TempDB and monitor it with sp_WhoIsActive on AdventureWorks2022.

First of all, run below Query Snippet-1 to monitor current utilization of your TempDB (Figure-1).

--Query Snippet-1
SELECT 
  -- Determining the amount of free space in tempdb
  SUM(unallocated_extent_page_count) AS [Free Pages] 
,(SUM(unallocated_extent_page_count)*1.0/128) AS [Free Space (MB)]
  -- Determining the amount of space used by the version store
, SUM(version_store_reserved_page_count) AS [Version Store Pages Used]
,(SUM(version_store_reserved_page_count)*1.0/128) AS [Version Store Space (MB)]
  -- Determining the amount of space used by internal objects
, SUM(internal_object_reserved_page_count) AS [Internal Object Pages used]
,(SUM(internal_object_reserved_page_count)*1.0/128) AS [Internal Object Space (MB)]
  -- Determining the amount of space used by user objects
, SUM(user_object_reserved_page_count) AS [User Object Pages Used]
,(SUM(user_object_reserved_page_count)*1.0/128) AS [User Object Space (MB)]
FROM tempdb.sys.dm_db_file_space_usage;

(Figure – 1: TempDB’s utilization before stress test)

Now create a stored procedure and paste Query Snippet-2 in SQLQueryStress to generate artificial load (Figure-2).

--Query Snippet-2
CREATE OR ALTER Proc proc_TempDB_Test
As
CREATE TABLE #T (
    Name NVARCHAR(500)
)
INSERT INTO #T
SELECT [FirstName] + ' ' + [MiddleName] FROM [Person].[Person]
 
-- Paste following query in SQLQueryStress.
DECLARE @i INT;
SET @i = 1;
WHILE @i <= 100
    BEGIN
        exec proc_TempDB_Test;
        SET @i = @i + 1;
    END

(Figure – 2: Putting stress on TempDB using SQLQueryStress)

Run Query Snippet-1 again to observe your TempDB utilization. You will see that free pages, spaces are reducing and user object pages and object spaces are increasing as shown below.

(Figure – 3: TempDB’s status during stress test)

Now run sp_WhoIsActive, you will see lots of PAGELATCH_EX wait types in TempDB as shown below.

(Figure – 4: Monitor TempDB using sp_WhoIsActive)

Best Practices for Contention Mitigation and Performance Enhancement

• Separate your TempDB from user database & log files, and put it on separate physical drives, preferably SSDs.
• Microsoft has following recommendations about TempDB data files to minimize GAM/SGAM/PFS page contention.
  • If the number of logical processors ≤ 8, use the same number of data files.
  • If the number of logical processors > 8, use eight data files.
  • If there is still contention, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload.
• Configure space for all TempDB files large enough to fit your workload, which prevents TempDB from expanding too often and improves performance.
• Cumulative updates – Microsoft often releases patches for bug fixing of TempDB contentions. Apply them regularly.

Planning for TempDB Capacity

For determining capacity in your production environment, analyze below in your test environment.

• Set auto grow on for TempDB.
• Monitor TempDB space, running individual queries or workload trace files.
• Execute index maintenance operations such as rebuilding indexes, and monitor TempDB space.
• Use the above value to predict your total workload usage. Adjust this value for projected concurrent activity, and then set the size of TempDB accordingly.

Next Steps

• Additional Reading
  • Five facts about the SQL Server Tempdb Database
  • SQL Server 2022 TempDB Contention Enhancements
  • SQL Server tempdb one or multiple data files
  • Recommendations to reduce allocation contention in SQL Server tempdb database
  • SQL SERVER – Who is Consuming my TempDB Now?
  • Mastering TempDB: The basics

M A A Mehedi Hasan

M A A Mehedi Hasan has been working with EBS Group since 2005. He started his career as a Software Developer and he is now Chief Technology Officer (CTO) of EBS Group. In his long journey, he had the opportunity to work with various technologies like the Microsoft Data Platform, SMS/USSD/IVR based Telecom Value Added Service, and Audio/Video streaming.

As a Microsoft certified Azure Database Administrator Associate, he manages large databases, testing and deployment, performance tuning, long term capacity planning, and streamlining operational workflow. He is also a certified AWS Cloud Practitioner and his team is migrating on-prem services to AWS.

Being a member of techforumbd, a Bangladeshi tech community, he regularly speaks and organizes sessions at local and virtual PASS chapters, SQL Saturdays, and Azure conferences. He also has a course about SQL Server available on the Ghoori Learning and Ostad.app platforms.

In his free time, he loves exploring and learning as much as possible about SQL Server.