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

---

TempDB is one of the most critical system databases in SQL Server. It is the shared workspace for every database on the instance: temporary tables, table variables, work tables, internal sort and hash spill operations, version store pages for snapshot isolation, and intermediate results from CTEs and subqueries all land in TempDB. Because every session on the instance shares it, misconfiguration creates bottlenecks that affect the entire workload simultaneously.

The most common TempDB tuning question is how many data files to configure. The answer is not a fixed number. It is a decision based on the server’s CPU count and measured latch contention, validated by monitoring and adjusted when the evidence says more files are needed.

1 Why Multiple Data Files Reduce Contention Beginner

With a single TempDB data file, every allocation request funnels through the same set of metadata pages: the Page Free Space (PFS) page, the Global Allocation Map (GAM), and the Shared Global Allocation Map (SGAM). Under concurrent workload, sessions compete for latches on these pages. The contention appears in wait statistics as PAGELATCH_UP or PAGELATCH_EX waits on TempDB allocation pages.

Multiple equally-sized data files distribute allocation requests across multiple sets of metadata pages. SQL Server uses a proportional fill algorithm that writes to each file in proportion to its free space, so files of equal size receive approximately equal allocation requests. This is not about storage throughput or making disks faster. It is specifically about reducing metadata page latch contention that blocks allocations from proceeding concurrently.

2 Microsoft’s Guidance on File Count Beginner

Microsoft’s official guidance on TempDB data file count has been consistent for many years and is still the correct starting point:

Server Logical CPU Count	Starting File Count	If Contention Persists
8 or fewer	Match file count to CPU count	Add files in increments of 4, do not exceed CPU count
More than 8	Start with 8 files	Add files in increments of 4, do not exceed CPU count

The staged approach avoids the common mistake of immediately creating one file per logical CPU on a 32 or 64 core server. That configuration adds management complexity and overhead without proportional benefit. Start at 8, measure contention, and add more only if the monitoring confirms it is needed.

3 File Configuration Best Practices Beginner

• Equal file sizes. SQL Server’s proportional fill algorithm distributes allocations in proportion to free space. Files of unequal size receive unequal allocation pressure, defeating the purpose of multiple files. All TempDB data files must be the same size.
• Equal autogrowth settings. If files grow at different rates, they become unequal over time and proportional fill breaks down. Set identical autogrowth sizes, not percentages, across all data files.
• Pre-size files to avoid autogrowth events. TempDB autogrowth during peak workload causes brief stalls. Estimate the typical TempDB usage for the workload and pre-size all files to cover it with room to spare. Setting a reasonable autogrowth increment is a safety net, not a primary sizing strategy.
• Dedicated fast storage. Place TempDB data and log files on the fastest available storage, separate from user database files. NVMe or dedicated SSD is ideal. TempDB I/O is synchronous and latency-sensitive, and it competes with user database I/O if placed on the same volume.
• One log file is correct. TempDB needs only one log file. Unlike data files, TempDB’s log does not benefit from multiple files and multiple log files are not supported.

4 Detecting TempDB Contention Intermediate

TempDB allocation contention appears as PAGELATCH_UP or PAGELATCH_EX waits where the resource description references TempDB allocation pages: PFS, GAM, or SGAM pages. These are distinct from PAGEIOLATCH waits, which indicate storage latency. The difference matters because the fix for latch contention (more files) is different from the fix for I/O latency (faster storage).

The resource description format for a TempDB allocation page latch looks like: 2:1:1 where 2 is the TempDB database_id, 1 is the file_id, and 1 is the page_id of the allocation page. PFS pages appear at every 8,088 pages. GAM pages appear at every 511,232 pages. Seeing waits concentrated on these specific page numbers confirms allocation contention rather than user data page contention.

-- Confirm PAGELATCH waits are on TempDB allocation pages (not user data)
SELECT
    wait_type,
    wait_time_ms,
    waiting_tasks_count,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE '2:%'  -- 2 = TempDB database_id
ORDER BY wait_time_ms DESC;

-- resource_description format: database_id:file_id:page_id
-- PFS pages:  page_id = 1, 8089, 16177, 24265 (every 8,088 pages)
-- GAM pages:  page_id = 2, 511234 (every 511,232 pages)
-- SGAM pages: page_id = 3
-- If waits concentrate on these page numbers: add TempDB data files
-- If waits are on other page numbers: investigate user object contention

5 TempDB Monitoring Scripts Intermediate

Current file sizes and free space

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

TempDB usage per session and task

-- Which sessions are consuming the most TempDB space right now
SELECT
    s.session_id,
    r.status,
    r.command,
    t.internal_objects_alloc_page_count    AS InternalPages,
    t.user_objects_alloc_page_count        AS UserPages,
    (t.internal_objects_alloc_page_count
     + t.user_objects_alloc_page_count) * 8 / 1024 AS TotalMB
FROM sys.dm_exec_requests              r
JOIN sys.dm_exec_sessions              s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage        t ON t.request_id = r.request_id
                                        AND t.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY TotalMB DESC;

Version store size (snapshot isolation workloads)

-- TempDB version store usage (relevant when RCSI or snapshot isolation is in use)
SELECT
    SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStoreMB,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalObjectsMB,
    SUM(user_object_reserved_page_count) * 8 / 1024 AS UserObjectsMB,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpaceMB
FROM sys.dm_db_file_space_usage;

6 Adding TempDB Data Files Beginner

TempDB file changes take effect immediately without a restart. The example below adds four files at once, which is the recommended increment when adding beyond the initial configuration. Adjust the file path, sizes, and names to match the environment.

-- Add 4 TempDB data files in one statement
-- Adjust FILENAME paths for your environment
-- SIZE and FILEGROWTH must match the existing data files exactly
USE [master];
GO

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

-- Verify all files are equal size after adding
SELECT
    file_id,
    name,
    size / 128.0 AS SizeMB,
    growth / 128.0 AS GrowthMB,
    physical_name
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
ORDER BY file_id;

7 PowerShell: Check File Count vs CPU Count Intermediate

This script checks the current TempDB data file count against the server’s logical CPU count and reports whether the configuration aligns with Microsoft’s starting guidance.

# Requires SqlServer PowerShell module
# Install-Module SqlServer if not present
Import-Module SqlServer

$Instance = "localhost"  # replace with target instance name

$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: Increase TempDB data files to match CPU count ($cpuCount)." -ForegroundColor Yellow
}
elseif ($cpuCount -gt 8 -and $tempdbFiles -lt 8) {
    Write-Host "Recommendation: Increase TempDB data files to 8 (Microsoft baseline)." -ForegroundColor Yellow
}
elseif ($cpuCount -gt 8 -and $tempdbFiles -ge 8 -and $tempdbFiles -lt $cpuCount) {
    Write-Host "Configuration meets baseline. Monitor for PAGELATCH contention." -ForegroundColor Green
    Write-Host "If contention persists, add files in increments of 4." -ForegroundColor Green
}
else {
    Write-Host "TempDB file count aligns with Microsoft guidance." -ForegroundColor Green
}

References

• Microsoft Docs: TempDB Database
• Microsoft Docs: sys.dm_db_file_space_usage
• Microsoft Docs: sys.dm_db_task_space_usage
• Microsoft Docs: sys.dm_os_waiting_tasks
• SQLYARD: When SQL Server Is Slow: Triage Playbook
• SQLYARD: SQL Server DMV Reference Cheat Sheet
• SQLYARD: SQL Server Instance Setup and Best Practices