SQL Server TempDB Files: How Many, Why, and When to Add More

Introduction

TempDB is one of the most critical system databases in SQL Server. It handles temporary objects, version stores, work tables, spills from insufficient memory, sorts, CTEs, and much more. Because it’s a shared workspace for every database on the instance, misconfiguration can lead to bottlenecks that impact your entire workload.

One of the most common tuning areas is the number of TempDB data files. Historically, administrators ran into allocation contention on metadata pages (PFS, GAM, SGAM). To fix that, Microsoft recommends spreading the workload across multiple equally sized data files.

But how many files do you really need? And how do you know when to add more? Let’s walk through the reasoning, best practices, and monitoring techniques.

---

Why Multiple Files?

With a single TempDB file, all allocations funnel through the same metadata pages, creating contention (often visible as PAGELATCH_UP or PAGELATCH_EX waits). Multiple files let SQL Server distribute allocations, reducing contention and improving concurrency.

This isn’t about storage throughput—it’s about metadata access. Adding more files doesn’t make disks faster, but it reduces latch contention that blocks queries from progressing.

---

Microsoft’s Recommendations

Microsoft’s guidance can be summarized as follows:

• If your server has ≤ 8 logical processors: Create the same number of TempDB data files as processors.
• If your server has > 8 logical processors: Start with 8 TempDB data files.
• If contention persists: Add more files in increments of 4, but never exceed the number of logical processors.

This staged approach keeps management overhead low and avoids the “32 cores = 32 files” mistake.

---

Best Practices for TempDB Files

• Equal sizes: All TempDB data files should be the same size. SQL Server uses proportional fill, so mismatched sizes cause uneven usage.
• Uniform autogrowth: Use the same autogrowth settings for all data files.
• Reasonable starting size: Pre-size files large enough to avoid constant growth events.
• Isolate TempDB storage: Place TempDB (data and log) on fast, dedicated storage (SSD or NVMe if possible).
• Trace flags (legacy only): On SQL Server 2014 and earlier, use TF 1118 to reduce SGAM contention. Newer versions handle this automatically.

---

When to Add More Than 8 Files

The magic number 8 solves most contention scenarios. But if you still see PAGELATCH waits on TempDB allocation pages after configuring 8 equal files, add more in increments of 4.

For example, if you’re on a 24-core server:

1. Start with 8 files.
2. If contention persists, grow to 12 files.
3. Re-evaluate. If needed, go to 16.
4. Stop once contention is resolved or you reach the number of logical CPUs.

---

How to Detect TempDB Contention

Look for PAGELATCH_UP or PAGELATCH_EX waits that are not IO-related. These point to allocation contention, not disk bottlenecks.

Useful DMVs include:

• sys.dm_os_waiting_tasks – shows latch contention.
• sys.dm_db_file_space_usage – monitors file usage.
• sys.dm_db_session_space_usage / sys.dm_db_task_space_usage – shows which sessions or tasks are consuming TempDB.

---

Example T-SQL Scripts

Check TempDB Usage by File

SELECT 
    file_id,
    name,
    type_desc,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM tempdb.sys.database_files;

Monitor TempDB Usage Per Session/Task

SELECT 
    s.session_id,
    r.status,
    r.command,
    t.internal_objects_alloc_page_count AS InternalPages,
    t.user_objects_alloc_page_count AS UserPages
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN sys.dm_db_task_space_usage t ON r.request_id = t.request_id
WHERE s.is_user_process = 1;

Check for PAGELATCH Contention (Hotspot Detection)

SELECT 
    wait_type, 
    wait_time_ms, 
    waiting_tasks_count, 
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%';

Add 4 TempDB Files (Automated Example)

USE [master];
GO
ALTER DATABASE tempdb 
ADD FILE (NAME = N'tempdev2', FILENAME = N'C:\TempDB\tempdb2.ndf', SIZE = 256MB, FILEGROWTH = 64MB),
           (NAME = N'tempdev3', FILENAME = N'C:\TempDB\tempdb3.ndf', SIZE = 256MB, FILEGROWTH = 64MB),
           (NAME = N'tempdev4', FILENAME = N'C:\TempDB\tempdb4.ndf', SIZE = 256MB, FILEGROWTH = 64MB),
           (NAME = N'tempdev5', FILENAME = N'C:\TempDB\tempdb5.ndf', SIZE = 256MB, FILEGROWTH = 64MB);
GO

PowerShell Script to Check TempDB Files and CPU Count

This script checks how many TempDB files you have versus how many logical processors are available. It will suggest adding more if you’re below Microsoft’s baseline guidance.

# Load SQL Server module (SQLPS or SqlServer)
Import-Module SqlServer

# Set your SQL instance
$Instance = "localhost"

# Query CPU count and TempDB files
$query = @"
SELECT 
    cpu_count = (SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND status = 'VISIBLE ONLINE'),
    tempdb_files = (SELECT COUNT(*) FROM tempdb.sys.database_files WHERE type_desc = 'ROWS');
"@

$result = Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query $query

$cpuCount = $result.cpu_count
$tempdbFiles = $result.tempdb_files

Write-Host "Logical CPU Count: $cpuCount"
Write-Host "Current TempDB Data Files: $tempdbFiles"

if ($cpuCount -le 8 -and $tempdbFiles -lt $cpuCount) {
    Write-Host "Recommendation: Match TempDB files to CPU count ($cpuCount)."
}
elseif ($cpuCount -gt 8 -and $tempdbFiles -lt 8) {
    Write-Host "Recommendation: Increase TempDB files to 8."
}
elseif ($cpuCount -gt 8 -and $tempdbFiles -ge 8 -and $tempdbFiles -lt $cpuCount) {
    Write-Host "If PAGELATCH contention persists, add files in increments of 4."
}
else {
    Write-Host "TempDB configuration looks appropriate based on Microsoft guidelines."
}

Final Thoughts

As a SQL Server DBA and architect, my approach with TempDB is about balancing simplicity with scalability. The goal isn’t to match file count to every logical CPU or to blindly follow outdated rules—it’s to configure TempDB in a way that reduces contention, keeps management straightforward, and is validated by monitoring real performance.

The formula is simple:

• If your server has 8 cores or fewer → Match the number of TempDB data files to the number of logical cores (e.g., 2 cores = 2 files, 4 cores = 4 files).
• If your server has more than 8 cores → Start with 8 TempDB data files.
• If contention persists → Add more files in increments of 4, but never exceed the number of logical processors.
• Always keep file sizes and growth settings identical across all TempDB data files.

That’s how you keep TempDB clean, predictable, and ready to support your workload without surprises.