Understanding SQL Server tempdb: Performance, Configuration and Troubleshooting

Introduction

Have you ever experienced a SQL Server that suddenly becomes slow, even though CPU and memory utilisation appear normal?

Or encountered a production database issue where queries start failing because the tempdb database has run out of space?

These problems are more common than many organisations realise.

tempdb is one of the most important system databases in Microsoft SQL Server. It supports temporary objects, query processing, sorting, internal operations and row versioning. Almost every SQL Server workload can depend on tempdb, even when applications do not explicitly create temporary tables.

Because tempdb is shared across an entire SQL Server instance, poor configuration, excessive space consumption or storage bottlenecks can affect multiple applications simultaneously.

For database administrators, understanding tempdb is essential for maintaining SQL Server performance, resolving unexpected slowdowns and preventing avoidable database incidents.

In this article, we explore how tempdb works, the common problems DBAs encounter, configuration best practices and practical T-SQL scripts to investigate tempdb performance.

1. What Is tempdb in SQL Server?

tempdb is a system database automatically created and maintained by Microsoft SQL Server.

Every SQL Server instance has a tempdb database, alongside other system databases such as master, model and msdb.

Unlike user databases, tempdb primarily stores temporary data and internal structures required during query processing and database operations.

Think of tempdb as SQL Server's shared working area.

Whenever SQL Server needs temporary storage to sort information, process intermediate results, manage certain transactions or maintain row versions, tempdb may be involved.

Common uses include:

• Temporary tables created by applications or stored procedures

• Intermediate results generated by SQL queries

• Sorting and hashing operations

• Temporary worktables and spools

• Query spills caused by insufficient execution memory grants

• Row versioning used by certain transaction isolation and database operations

• Temporary storage required by database maintenance operations

Because all databases on an instance share tempdb, an inefficient query from one application can potentially affect the performance of other databases.

Why tempdb matters for production SQL Server environments

A poorly configured or overloaded tempdb can contribute to:

• Slow application response times

• Increased query execution times

• SQL Server blocking and resource contention

• Failed queries and transactions

• Excessive storage I/O

• Unexpected disk space consumption

• Database maintenance failures

For business-critical SQL Server environments, tempdb performance should be included in regular database health checks.

2. What Does SQL Server Store in tempdb?

SQL Server primarily uses tempdb for three categories of objects and data.

User objects

Applications and stored procedures frequently create temporary objects to process intermediate results.

For example:

CREATE TABLE #SalesSummary
(
    CustomerID INT,
    TotalSales DECIMAL(18,2)
);

INSERT INTO #SalesSummary
VALUES (101, 15000.00);

SELECT *
FROM #SalesSummary;

DROP TABLE #SalesSummary;

The temporary table is stored in tempdb.

Large temporary tables or frequent creation and deletion of temporary objects can increase tempdb workload.

Internal objects

SQL Server also uses tempdb internally when processing complex queries.

For example, queries involving large sorts, hash joins, aggregations and intermediate results may require additional workspace.

When an operation cannot complete entirely within its allocated execution memory, SQL Server may spill data to tempdb.

These spills introduce additional storage I/O and can significantly increase query execution time.

Version store

SQL Server uses row versioning to support certain concurrency and database operations.

When Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation is enabled, SQL Server can maintain older row versions so that readers can access consistent data without requiring the same shared locking behaviour as traditional read committed isolation.

Long-running transactions can prevent old row versions from being cleaned up, causing the version store to grow.

Important: On SQL Server versions and databases using Accelerated Database Recovery (ADR), persistent version store behaviour differs. ADR uses a persistent version store in the user database, so DBAs should not assume every row-versioning workload consumes tempdb version store space in the same way.

3. How Does tempdb Behave Differently from Other SQL Server Databases?

tempdb has several characteristics that distinguish it from user databases.

tempdb is recreated when SQL Server starts

Every time the SQL Server Database Engine starts, tempdb is recreated.

This means temporary objects and data from the previous SQL Server session are discarded.

The database is recreated using its configured file locations and initial file sizes.

This behaviour makes correct file sizing particularly important.

If tempdb regularly grows to 60 GB during normal operations but its initial size is only 8 GB, SQL Server may need to repeatedly grow files following a restart.

tempdb cannot be backed up or restored

SQL Server does not support conventional database backup and restore operations for tempdb.

Unlike user databases, tempdb is designed to contain temporary working data.

tempdb uses the SIMPLE recovery model

The tempdb database always uses the SIMPLE recovery model.

However, this does not mean its transaction log cannot become full. Long-running transactions and other operations can still prevent transaction log space from being reused.

tempdb is essential for SQL Server operations

tempdb cannot be dropped or taken offline during normal SQL Server operations.

A failure to create or access the configured tempdb files during SQL Server startup can prevent the Database Engine from starting successfully.

4. Common SQL Server tempdb Performance Problems

Understanding the most common tempdb issues helps DBAs identify problems before they affect production applications.

Problem 1: Allocation contention and PAGELATCH waits

One common tempdb performance issue is allocation contention.

When many sessions simultaneously create or modify temporary objects, they can compete for access to internal allocation pages.

This contention may appear as:

• PAGELATCH_UP

• PAGELATCH_EX

These waits are associated with in-memory page latches and do not necessarily indicate slow physical disk I/O.

Relevant allocation structures include:

• PFS – Page Free Space

• GAM – Global Allocation Map

• SGAM – Shared Global Allocation Map

When multiple SQL Server sessions compete for these structures, query performance can deteriorate.

Example scenario

An application runs hundreds of concurrent stored procedures that repeatedly create and drop temporary tables.

During peak business hours, application response times increase.

SQL Server monitoring shows high PAGELATCH_UP waits on tempdb allocation pages.

The DBA discovers that tempdb has only one data file despite a highly concurrent workload.

Adding appropriately configured tempdb data files may help distribute allocation activity and reduce contention.

However, the DBA must first confirm the wait resources and underlying cause, because not every PAGELATCH wait is solved by adding files.

Problem 2: tempdb runs out of space

A full tempdb database can cause SQL queries and application operations to fail.

Common error messages include:

SQL Server Error 1105

Could not allocate space for object in database
'tempdb' because the filegroup is full.

SQL Server Error 9002

The transaction log for database 'tempdb'
is full.

Potential causes include:

• Large temporary tables

• Excessive query spills

• Long-running transactions

• Large sorting or hashing operations

• Version store growth

• Insufficient disk capacity

• Restrictive file growth settings

Increasing tempdb capacity may restore operations temporarily, but DBAs should identify the workload responsible for the unexpected growth.

Problem 3: Uneven tempdb data file sizes

SQL Server uses proportional-fill allocation to distribute data across files within a filegroup.

If tempdb data files have substantially different sizes or available space, allocation activity may be uneven.

For example:

This configuration may cause uneven allocation behaviour and reduce the benefit of multiple data files.

A better starting point is to configure equal-sized data files with consistent autogrowth settings.

Problem 4: Slow storage performance

tempdb can generate substantial read and write activity.

Slow storage can affect workloads that perform large sorts, hash joins, temporary table operations or repeated spills.

Potential symptoms include:

• High PAGEIOLATCH waits

• Elevated storage read or write latency

• Slow query execution

• High tempdb I/O activity

• Increased application response times

Sustained storage latency above approximately 20 milliseconds may warrant investigation for latency-sensitive workloads, although acceptable values depend on storage architecture and workload requirements.

The solution may involve faster storage, reducing excessive tempdb I/O or optimising inefficient queries.

Problem 5: tempdb shares storage with critical databases

When tempdb shares a disk or volume with user database files, unexpected tempdb growth can consume capacity required by production databases.

This may affect database autogrowth, transaction logging or other critical operations.

Where appropriate, separate tempdb storage capacity and performance resources from critical user database workloads.

In cloud environments, evaluate the actual underlying storage architecture, including IOPS, throughput, latency and temporary storage persistence.

5. SQL Server tempdb Configuration Best Practices

Microsoft SQL Server performance depends on workload characteristics, available infrastructure and database configuration.

The following recommendations provide a practical starting point for DBAs.

5.1 Configure an appropriate number of tempdb data files

Multiple data files can reduce certain forms of allocation contention.

A commonly recommended starting approach is:

• Up to eight logical processors: consider one tempdb data file per logical processor.

• More than eight logical processors: begin with eight data files.

• If confirmed allocation contention continues, consider increasing the file count in increments of four.

However, this is a starting guideline, not a universal requirement.

For lightly loaded SQL Server instances, fewer files may be sufficient.

Adding more files than necessary increases management complexity without guaranteeing improved performance.

5.2 Keep tempdb data files equally sized

Configure tempdb data files with:

• Equal initial sizes

• Consistent autogrowth settings

• Sufficient capacity for expected workloads

Equal file sizes help maintain more predictable proportional-fill behaviour.

5.3 Pre-size tempdb files

Instead of relying on repeated autogrowth events, configure file sizes based on observed peak workload requirements.

For example, if normal peak usage is approximately 24 GB, the DBA might provision an appropriate initial capacity across multiple data files, with additional space available for workload growth.

The exact allocation should be based on measured usage, workload peaks and available capacity.

5.4 Use fixed autogrowth increments

Fixed-size growth settings are generally more predictable than percentage-based growth.

Examples include 256 MB or 512 MB per data file, although larger or smaller values may be appropriate depending on workloads and storage performance.

Ensure sufficient free capacity remains on the underlying storage.

5.5 Use suitable high-performance storage

tempdb is often an I/O-intensive database.

Storage selection should account for:

• Read and write latency

• Provisioned IOPS

• Throughput

• Concurrent workload activity

• Available storage capacity

Where appropriate, separating tempdb from other database workloads can help isolate performance and capacity risks.

5.6 Use Instant File Initialization

Instant File Initialization can reduce the time required for eligible SQL Server data file growth operations.

It is typically enabled through the Windows Perform volume maintenance tasks security privilege for the SQL Server service account.

This can improve file growth performance, although it does not eliminate the need for adequate initial sizing.

5.7 Evaluate SQL Server version-specific optimisations

SQL Server versions have introduced improvements to reduce certain forms of tempdb contention.

For example, SQL Server 2019 introduced memory-optimised tempdb metadata, which can help address metadata contention in supported scenarios.

This feature requires evaluation and configuration; it should not automatically be enabled without considering workload characteristics and operational requirements.

Later SQL Server releases also include improvements to concurrency and allocation behaviour.

5.8 Optimise queries that consume excessive tempdb resources

Adding files or storage capacity will not always fix a tempdb problem.

Excessive tempdb activity may indicate inefficient SQL queries.

DBAs should investigate:

• Large sort operations

• Hash spills

• Poor execution plans

• Missing or inefficient indexes

• Inaccurate statistics

• Excessive temporary object creation

• Long-running transactions

• Unexpected version store growth

In many cases, optimising the query provides a more sustainable improvement than simply increasing tempdb capacity.

6. Practical T-SQL Queries to Monitor and Troubleshoot tempdb

The following diagnostic queries can help SQL Server DBAs investigate tempdb configuration, space consumption and contention.

Query 1: Check tempdb files, locations and growth settings

Use this query to identify where tempdb files are located, their configured sizes and autogrowth settings.

SELECT
    name AS LogicalFileName,
    physical_name AS PhysicalFileName,
    type_desc AS FileType,
    CAST(size * 8.0 / 1024 AS DECIMAL(18,2))
        AS SizeMB,
    CASE
        WHEN is_percent_growth = 1
            THEN CAST(growth AS VARCHAR(20)) + '%'
        ELSE
            CAST(growth * 8.0 / 1024
                 AS VARCHAR(20)) + ' MB'
    END AS AutoGrowth
FROM tempdb.sys.database_files
ORDER BY type_desc, name;

What to look for:

• Uneven data file sizes

• Unexpected file locations

• Percentage-based file growth

• Incorrect storage paths

• Insufficient initial file sizes

Query 2: Analyse tempdb space consumption

This query shows how tempdb data-file space is distributed across user objects, internal objects, version store and unallocated space.

SELECT
    CAST(
        SUM(user_object_reserved_page_count)
        * 8.0 / 1024 AS DECIMAL(18,2)
    ) AS UserObjectsMB,

    CAST(
        SUM(internal_object_reserved_page_count)
        * 8.0 / 1024 AS DECIMAL(18,2)
    ) AS InternalObjectsMB,

    CAST(
        SUM(version_store_reserved_page_count)
        * 8.0 / 1024 AS DECIMAL(18,2)
    ) AS VersionStoreMB,

    CAST(
        SUM(unallocated_extent_page_count)
        * 8.0 / 1024 AS DECIMAL(18,2)
    ) AS UnallocatedMB
FROM tempdb.sys.dm_db_file_space_usage;

Interpretation:

If internal objects consume substantial space, investigate expensive query operations and spills.

If user objects dominate, investigate temporary tables and application workloads.

If the version store is unusually large, review long-running transactions and row-versioning activity.

Query 3: Identify sessions consuming tempdb space

The following query uses session-level allocation and deallocation counters to identify sessions associated with significant tempdb usage.

SELECT TOP (10)
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,

    CAST(
        (u.user_objects_alloc_page_count
         - u.user_objects_dealloc_page_count)
        * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS NetUserObjectsMB,

    CAST(
        (u.internal_objects_alloc_page_count
         - u.internal_objects_dealloc_page_count)
        * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS NetInternalObjectsMB

FROM sys.dm_db_session_space_usage AS u
JOIN sys.dm_exec_sessions AS s
    ON u.session_id = s.session_id

ORDER BY
    (u.user_objects_alloc_page_count
     - u.user_objects_dealloc_page_count
     + u.internal_objects_alloc_page_count
     - u.internal_objects_dealloc_page_count)
    DESC;

Important: Session-level counters are not a complete real-time measure of every active request's current tempdb footprint. Active tasks can have allocations not yet reflected in session totals. Use sys.dm_db_task_space_usage for more detailed request-level investigations.

Query 4: Identify tempdb PAGELATCH contention

SELECT
    session_id,
    wait_type,
    wait_duration_ms,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE '2:%'
ORDER BY wait_duration_ms DESC;

What to look for:

• Multiple sessions waiting on the same allocation resources

• Repeated PAGELATCH_UP or PAGELATCH_EX waits

• Sustained contention during application peak periods

A resource description beginning with 2: identifies tempdb.

Investigate the specific file and page to determine whether the contention relates to allocation structures, metadata or another page type.

Query 5: Identify tempdb file I/O latency

SELECT
    mf.name AS LogicalFileName,
    mf.physical_name AS PhysicalFileName,

    vfs.num_of_reads,
    vfs.num_of_writes,

    CAST(
        vfs.io_stall_read_ms * 1.0 /
        NULLIF(vfs.num_of_reads, 0)
        AS DECIMAL(18,2)
    ) AS AvgReadLatencyMS,

    CAST(
        vfs.io_stall_write_ms * 1.0 /
        NULLIF(vfs.num_of_writes, 0)
        AS DECIMAL(18,2)
    ) AS AvgWriteLatencyMS

FROM sys.dm_io_virtual_file_stats(2, NULL) AS vfs
JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id
   AND vfs.file_id = mf.file_id;

This query helps identify potential storage latency issues affecting tempdb files.

The values are averages over the period represented by the cumulative counters, rather than instantaneous latency measurements. Sample the counters over defined intervals for more meaningful workload analysis.

7. Example: Configuring Multiple tempdb Data Files

Consider a SQL Server instance configured with eight logical processors and a workload where allocation contention has been confirmed.

The DBA decides to configure eight equally sized tempdb data files.

The following commands demonstrate how to modify the initial data file and add additional files.

Example only: Adjust the file paths, number of files, initial sizes and growth settings to match the actual SQL Server instance and storage capacity.

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = tempdev,
    SIZE = 2048MB,
    FILEGROWTH = 512MB
);
GO

ALTER DATABASE tempdb
ADD FILE
(
    NAME = tempdev2,
    FILENAME = 'T:\MSSQL\TempDB\tempdb2.ndf',
    SIZE = 2048MB,
    FILEGROWTH = 512MB
);
GO

ALTER DATABASE tempdb
ADD FILE
(
    NAME = tempdev3,
    FILENAME = 'T:\MSSQL\TempDB\tempdb3.ndf',
    SIZE = 2048MB,
    FILEGROWTH = 512MB
);
GO

Repeat the ADD FILE command, using unique logical and physical file names, to reach the required number of files.

Before execution, verify that the target directory exists, the SQL Server service account has appropriate permissions and the storage has sufficient capacity.

The initial size shown here is illustrative, not a recommendation that every SQL Server instance should use 2 GB per file.

Review file configuration and performance after implementation to determine whether allocation contention has improved.

8. Moving tempdb to Another Drive

Moving tempdb may be necessary when:

• The existing disk is running out of space

• The organisation is replacing storage infrastructure

• tempdb requires a faster storage volume

• SQL Server database files are being reorganised

• The environment is being migrated to a new storage architecture

Because SQL Server recreates tempdb when the Database Engine starts, relocating tempdb generally involves modifying the file paths and restarting the SQL Server service.

For example:

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = tempdev,
    FILENAME = 'T:\MSSQL\TempDB\tempdb.mdf'
);
GO

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = templog,
    FILENAME = 'T:\MSSQL\TempDB\templog.ldf'
);
GO

Any additional tempdb data files must also be updated individually.

The new file locations take effect when SQL Server restarts and recreates tempdb.

Production considerations:

• Schedule a suitable maintenance window.

• Create the destination folders beforehand.

• Verify SQL Server service account permissions.

• Confirm all configured tempdb file paths.

• Ensure sufficient disk space exists.

• Validate SQL Server startup and tempdb file locations after restart.

• Do not remove the original storage until migration validation is complete.

An incorrect tempdb path or missing permissions can prevent SQL Server from starting.

9. SQL Server tempdb Troubleshooting Checklist

When investigating tempdb performance issues, use a structured approach.

The most effective approach is to identify the root cause before making configuration changes.

For example, increasing the number of tempdb files may help allocation contention, but it will not necessarily resolve a large version store caused by a long-running transaction.

Similarly, moving tempdb to faster storage may reduce I/O latency without fixing an inefficient stored procedure that repeatedly spills millions of rows.

10. Proactive tempdb Monitoring and Capacity Planning

Waiting until tempdb fills up is not an effective strategy for production database management.

A proactive monitoring process should track:

• tempdb total and available space

• Data and log file growth

• Storage latency

• PAGELATCH contention

• Unexpected increases in internal object usage

• Version store growth

• Query spills and expensive SQL statements

• Disk capacity trends

Monitoring thresholds should be based on workload baselines rather than a single universal percentage.

For example, an unexpected rise from 20 GB to 80 GB during normal activity may be more significant than an instance consistently using a large but well-understood amount of tempdb space.

DBAs should also validate alerts and escalation procedures so that critical events are investigated before they affect application availability.

11. When Should You Review Your SQL Server tempdb Configuration?

A tempdb review is particularly useful when:

• SQL Server performance degrades under concurrent workloads.

• Applications experience intermittent slow queries.

• tempdb regularly grows beyond its configured initial size.

• Database operations fail because of insufficient tempdb space.

• Monitoring shows sustained PAGELATCH contention.

• Storage latency increases during peak activity.

• SQL Server is upgraded or migrated to new infrastructure.

• New business applications significantly increase database workloads.

• An organisation is planning SQL Server consolidation or cloud migration.

Regular tempdb assessments can help identify bottlenecks before they become production incidents.

12. How Onsys Technologies Helps Optimise SQL Server Performance

SQL Server performance issues are not always caused by insufficient infrastructure resources.

In many cases, configuration problems, inefficient SQL queries, unexpected workload behaviour or inadequate monitoring contribute to database slowdowns.

At Onsys Technologies, we help organisations across Australia and the Pacific investigate, optimise and maintain SQL Server environments.

Our SQL Server consulting and DBA services can assist with:

SQL Server Health Checks

Review database configuration, performance indicators, capacity, availability and operational risks.

SQL Server Performance Tuning

Identify slow-running queries, inefficient execution plans, indexing problems, I/O bottlenecks and resource contention.

tempdb Configuration and Troubleshooting

Investigate allocation contention, space consumption, storage configuration and tempdb-related performance problems.

SQL Server Migration and Upgrades

Plan database migrations, infrastructure changes and SQL Server upgrades while considering performance and operational requirements.

Remote DBA and Managed SQL Server Support

Provide ongoing database administration, monitoring, troubleshooting, maintenance and performance optimisation services.

Our objective is to help organisations maintain reliable SQL Server environments and reduce the risk of database-related business disruption.

Frequently Asked Questions About SQL Server tempdb

What happens when tempdb becomes full?

When tempdb runs out of available data or transaction log space, SQL Server operations requiring additional space may fail.

Depending on the workload, this can affect application queries, temporary tables, sorting operations and other database tasks.

Should tempdb have multiple data files?

Multiple data files can help reduce certain forms of allocation contention.

A commonly used starting guideline is up to eight data files, based on the number of logical processors. However, the appropriate configuration depends on workload characteristics and measured contention.

Does restarting SQL Server clear tempdb?

Yes. SQL Server recreates tempdb during Database Engine startup.

However, restarting SQL Server is not a substitute for identifying the cause of excessive tempdb growth.

If the workload remains unchanged, the same problem may reappear.

Should tempdb be placed on a separate drive?

Dedicated or isolated high-performance storage can be beneficial for busy SQL Server environments.

The decision should consider workload intensity, available infrastructure, storage latency and capacity requirements.

Can a slow query cause tempdb to fill up?

Yes. Queries involving large sorts, hash operations, intermediate results or extensive temporary tables can consume significant tempdb space.

Poor execution plans and inadequate memory grants may also increase tempdb usage.

Can SQL Server tempdb be backed up?

No. SQL Server does not support conventional backup and restore operations for tempdb because it is recreated when the Database Engine starts.

Does adding more tempdb files always improve performance?

No. Additional files primarily help when specific types of allocation contention are present.

Adding files will not automatically resolve excessive query spills, version store growth or slow storage.

How often should tempdb be monitored?

For production SQL Server instances, tempdb capacity and critical performance indicators should be monitored continuously or at intervals appropriate to business requirements.

Historical baselines and alerts help DBAs distinguish normal workload behaviour from emerging problems.

Conclusion

tempdb plays a critical role in Microsoft SQL Server performance, yet it is often overlooked until applications slow down or database operations begin to fail.

Understanding how tempdb manages temporary objects, internal query processing and row versions allows DBAs to identify performance problems more effectively.

The key to successful tempdb management is not simply adding more files or increasing disk capacity.

It is understanding the workload, configuring tempdb appropriately, monitoring resource consumption and addressing the underlying causes of performance bottlenecks.

With the right configuration and proactive database administration, organisations can reduce unexpected tempdb-related incidents and improve the reliability of their SQL Server environments.

Need Help Troubleshooting SQL Server Performance?

Are you experiencing SQL Server performance issues, excessive tempdb growth, slow queries or unexpected database resource contention?

Onsys Technologies can help.

Our SQL Server consultants provide database health checks, performance optimisation, troubleshooting, migration and ongoing DBA services for organisations across Australia and the Pacific.

Start with a free 20-point SQL Server Health Check or speak with our team about your database performance requirements.

Visit https://www.onsys.com.au to learn more about our SQL Server consulting and managed DBA services.