DBA Scripts: Get TempDB Configuration

TempDB is shared by every database on the instance, and it’s usually the first thing to show contention under load, long before any user database does. Unlike a user database’s files, TempDB’s default configuration out of the box (a single data file) is wrong for almost any real workload, and the fix is well documented, but it’s exactly the kind of setting that gets configured once at install time and never revisited.

This script checks the three things that actually matter: file count relative to CPU cores, whether the files are sized equally, and whether autogrowth is set as a fixed amount rather than a percentage.

Why TempDB Configuration Matters

TempDB backs far more than explicit #temp tables, it’s where SQL Server stores sort spills, hash join work tables, index rebuild intermediate data, cursor materialisation, triggers, and version store rows for snapshot isolation. Every session on the instance shares the same TempDB files, which makes its configuration a shared resource problem, not a per-database one:

• Too few data files relative to CPU cores creates allocation page (PFS/GAM/SGAM) contention as multiple threads fight over the same allocation structures, this is the single most common TempDB misconfiguration.
• Unequal file sizes break SQL Server’s proportional fill algorithm, which allocates more heavily to whichever file has the most free space, so uneven files end up unevenly used, defeating the point of having multiple files.
• Percentage-based autogrowth on files that are already large causes autogrowth events that take longer and longer as the file grows, a fixed MB amount keeps growth events predictable in duration.

Common Symptoms

• How to Move TempDB Files, relocating them once the configuration tells you they are in the wrong place
• PAGELATCH_UP waits on TempDB’s allocation pages under concurrent load.
• TempDB growth events showing up in the default trace or error log at inconsistent, escalating intervals.
• A newly built or migrated server still running with TempDB’s single-file default.
• General “everything is slow” symptoms during heavy batch or ETL windows that trace back to TempDB contention rather than the user database itself.

When to Run This Script

• Routine SQL Server health checks
• Immediately after standing up a new instance, TempDB’s defaults are rarely production-appropriate
• After a migration, to confirm the target server’s TempDB was configured to match the source (or better)
• Investigating PAGELATCH contention or general slowness under concurrent load

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-TempDbConfiguration
Category    : monitoring
Purpose     : Reviews TempDB file configuration — file count, sizing parity, autogrowth
              settings, and max server memory context. Surfaces common misconfigurations
              that cause allocation contention on busy OLTP servers.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-temp-db-configuration/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  DESIGN: Best practice guidance embedded in status columns:
    - Equal file count to physical CPU cores (up to 8) prevents allocation hotspots
    - All data files should be equal size (unequal sizing causes uneven proportional fill)
    - Fixed-size autogrowth (not %) is strongly preferred for TempDB
    - Instant file initialization (IFI) cannot be detected here but matters for autogrowth speed
  Returns one row per TempDB file with a per-file status, plus a summary row (row_type='summary').
*/

WITH tempdb_files AS (
    SELECT
        mf.file_id,
        mf.name AS logical_name,
        mf.physical_name,
        mf.type_desc AS file_type,
        CAST(mf.size * 8.0 / 1024 AS decimal(10,2)) AS file_size_mb,
        CASE WHEN mf.max_size = -1 OR mf.max_size = 268435456
             THEN NULL
             ELSE CAST(mf.max_size * 8.0 / 1024 AS decimal(10,2))
        END AS max_size_mb,
        mf.is_percent_growth,
        CASE WHEN mf.is_percent_growth = 1
             THEN CAST(mf.growth AS varchar(10)) + '%'
             ELSE CAST(mf.growth * 8 / 1024 AS varchar(10)) + ' MB'
        END AS autogrowth_setting
    FROM sys.master_files mf
    WHERE mf.database_id = 2 -- TempDB
),
sizing_stats AS (
    SELECT
        file_type,
        COUNT(*) AS file_count,
        MIN(file_size_mb) AS min_size_mb,
        MAX(file_size_mb) AS max_size_mb,
        AVG(file_size_mb) AS avg_size_mb
    FROM tempdb_files
    GROUP BY file_type
)
SELECT
    f.file_type,
    f.file_id,
    f.logical_name,
    f.physical_name,
    f.file_size_mb,
    f.max_size_mb,
    f.autogrowth_setting,
    CASE WHEN f.is_percent_growth = 1
         THEN 'WARN — percent autogrowth; use fixed MB for TempDB'
         ELSE 'OK'
    END AS autogrowth_status,
    CASE WHEN s.min_size_mb = s.max_size_mb OR s.file_count = 1
         THEN 'OK — equal sizing'
         ELSE 'WARN — files are unequal (' + CAST(s.min_size_mb AS varchar) + '–' +
              CAST(s.max_size_mb AS varchar) + ' MB); equal sizing prevents allocation contention'
    END AS sizing_status,
    s.file_count AS total_files_this_type,
    (SELECT CAST(value_in_use AS varchar(20))
     FROM sys.configurations WHERE name = 'max degree of parallelism') AS maxdop,
    (SELECT cpu_count FROM sys.dm_os_sys_info) AS logical_cpu_count,
    CASE
        WHEN f.file_type = 'ROWS' AND s.file_count < (SELECT CASE WHEN cpu_count > 8 THEN 8 ELSE cpu_count END FROM sys.dm_os_sys_info)
            THEN 'WARN — ' + CAST(s.file_count AS varchar) + ' data file(s); recommend 1 per core up to 8'
        WHEN f.file_type = 'ROWS' AND s.file_count > 8
            THEN 'INFO — ' + CAST(s.file_count AS varchar) + ' data files; > 8 rarely helps'
        ELSE 'OK'
    END AS file_count_status
FROM tempdb_files f
JOIN sizing_stats s ON s.file_type = f.file_type
ORDER BY f.file_type DESC, f.file_id;

This reads every TempDB file from sys.master_files, computes sizing parity and autogrowth status per file, and cross-references the data file count against the instance’s logical CPU count to flag whether TempDB has enough files to avoid allocation contention.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check TempDB file count, sizing parity, and autogrowth settings:
.\run.ps1 Get-TempDbConfiguration

# To run against a remote sql server:
.\run.ps1 Get-TempDbConfiguration -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/tempdb/Get-TempDbConfiguration.sql
• powershell/wrappers/monitoring/tempdb/Get-TempDbConfiguration.ps1

Example Output

Run against the lab instance. 9 rows come back: 8 data files and 1 log file, with every status column reading OK. That is the answer you want, and it is worth showing, because a clean result here is a complete answer rather than a failed run.

2 things are worth reading past the OKs. The data files are 8 MB each with 64 MB autogrowth, which is exactly the SQL Server install default, so equal sizing is satisfied trivially: nothing has grown yet. And templog sits at 1,096 MB against 8 MB data files, so the log has grown while the data files have not. On a lightly used instance that is normal, and it is the shape to look at again once real work has run through it.

The status columns repeat on every row because they describe the instance rather than the file. Read 1 row for those, then read the rest for the per-file detail.

This box happens to already be configured correctly, 8 equally sized data files matching its 8 logical cores, fixed 64 MB autogrowth, everything reporting OK, a genuinely well-configured example rather than a manufactured problem.

Understanding the Results

Common Causes

Most TempDB misconfiguration traces back to one root cause: nobody changed it after install. SQL Server’s out-of-box default is a single data file that autogrows in fixed increments too small for a busy server, a setting chosen decades ago for compatibility, not for OLTP concurrency. Migrations and restores compound this, a database moved to new hardware doesn’t bring TempDB’s file layout with it, TempDB is rebuilt fresh on every instance and inherits whatever that instance’s install-time defaults were.

How to Fix TempDB Configuration

Add data files to match CPU count (up to 8, then reassess only if allocation contention persists):

ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev2,
    FILENAME = 'D:\TempDB\tempdev2.ndf',
    SIZE = 8192KB,
    FILEGROWTH = 65536KB   -- fixed 64 MB, not percent
);

Repeat per file needed, keeping every file’s SIZE and FILEGROWTH identical to the existing ones. New files apply after the next SQL Server restart.

Fix percentage-based autogrowth to a fixed amount:

ALTER DATABASE tempdb MODIFY FILE (
    NAME = tempdev,
    FILEGROWTH = 65536KB   -- 64 MB, adjust to match your file size
);

Run this against every TempDB file individually, sys.master_files from the script above shows exactly which ones need it.

Best Practices

• Match TempDB data file count to logical CPU count, capped at 8, more files beyond that rarely helps and adds management overhead.
• Keep every TempDB data file the exact same size, always grow them together if you resize.
• Use fixed MB autogrowth, never percentage, for every TempDB file.
• Check this immediately after any new instance build, restore to new hardware, or migration, it’s a five-minute fix that’s easy to forget under deadline pressure.
• Re-run this script as part of routine health checks, TempDB configuration doesn’t drift on its own, but a well-meaning “quick fix” file add during an incident sometimes leaves files unequal afterward.

Microsoft’s reference covers sys.master_files, sys.dm_os_sys_info and sys.configurations in full.

Related Scripts

You may also find these scripts useful:

• TempDB Hotspots
• TempDB Usage and File Balance
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

TempDB is one of the few settings that’s genuinely install-once, verify-forever, it doesn’t change with the workload, so a misconfiguration found on day one is usually still there years later unless someone specifically checks. This script turns that check into thirty seconds: file count against CPU cores, sizing parity, and autogrowth type, all in one pass.

Run it after every new instance build and every migration, and again any time TempDB contention shows up as a symptom rather than a known cause.