tempdb best practices (file count strategy, growth, contention reduction)

Section 7 — Lesson 5: tempdb Best Practices (File Count Strategy, Growth, Contention Reduction)

Welcome back. If you ask experienced DBAs to name the most common hidden bottleneck in SQL Server,
many will say tempdb. tempdb is a shared system database used by almost everything:
sorting, hashing, versioning, temporary objects, row version store, internal worktables, and many engine operations.

The problem is not that tempdb exists. The problem is that tempdb is a shared resource.
In high concurrency, a poorly configured tempdb can cause widespread slowdowns across all databases on the instance.

Lesson description: You will learn what tempdb is used for, why tempdb can become a bottleneck,
and how DBAs configure tempdb to prevent contention: multiple data files (balanced sizes), sane fixed autogrowth,
and modern contention-reduction features. You will practice auditing your current tempdb configuration, standardizing it,
and documenting it using a professional baseline → change → validate workflow.

1) What tempdb Is (Plain English)

tempdb is a system database that SQL Server recreates every time the instance starts.
Think of it as SQL Server’s “workspace.”

tempdb is used for:

• Temporary tables and table variables (e.g., #temp)
• Sorts and hash operations (when memory is insufficient or for large operations)
• Worktables for intermediate query processing
• Row versioning (e.g., snapshot isolation / RCSI version store)
• Index rebuild operations (depending on options)
• Spills from memory grants (sort/hash spill to disk)

DBA takeaway: Even if your user databases are “fine,” tempdb issues can slow down the entire instance.

2) Why tempdb Becomes a Bottleneck (The Three Big Causes)

2.1 Allocation contention

Many concurrent operations allocate and deallocate tempdb pages. If the allocation structures become hot,
sessions can wait on each other, creating system-wide slowdowns.

2.2 I/O bottlenecks

If tempdb is on slow storage or shares a busy disk with other files, heavy tempdb usage can cause high latency,
leading to query stalls and timeouts.

2.3 Poor file configuration (single file, uneven files, bad growth)

A single tempdb data file under high concurrency increases contention.
Percent growth and tiny growth increments can cause frequent growth events and fragmentation.

3) The tempdb File Strategy (The DBA Standard)

DBAs typically configure tempdb with these principles:

1. Multiple tempdb data files to reduce allocation contention
2. Equal file sizes so SQL Server uses them evenly (balanced allocations)
3. Fixed autogrowth increments (not percent) and consistent across files
4. Pre-size files to avoid frequent autogrowth
5. Keep tempdb log reasonable (also with fixed growth) and monitor it

Important nuance: Adding “too many” data files can also be unnecessary.
DBAs aim for a balanced approach, not “maximum files.”

4) File Count Strategy (How Many tempdb Data Files?)

tempdb file count is one of the most talked-about DBA topics because it depends on CPU concurrency and workload.
The guiding principle is:

A common professional starting point in many environments is to create multiple tempdb data files
(often aligned with CPU usage patterns) and then adjust based on evidence.

Lab approach: You will practice the method:
baseline → configure a reasonable number of equal files → validate.

5) Evidence First: Audit Your Current tempdb Configuration

Run this to see tempdb file layout, sizes, and growth settings.

USE tempdb;
GO

SELECT
    DB_NAME() AS database_name,
    mf.file_id,
    mf.type_desc,
    mf.name AS logical_name,
    mf.physical_name,
    mf.size * 8 / 1024 AS size_mb,
    CASE
        WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS varchar(20)) + '%'
        ELSE CAST((mf.growth * 8) / 1024 AS varchar(20)) + ' MB'
    END AS autogrowth_setting,
    mf.is_percent_growth
FROM sys.database_files AS mf
ORDER BY mf.type_desc, mf.file_id;

Save output to:
C:\DBA\Baselines\InstanceConfig\tempdb_files_before_YYYYMMDD_HHMM.txt

6) Evidence: Detect tempdb Contention Signals (Waits and Usage)

You cannot tune tempdb correctly without evidence. Here are two practical signals:
tempdb wait patterns and tempdb space usage.

6.1 Wait stats scan (look for contention patterns)

This script shows top waits. You will interpret results in the Monitoring section later,
but for now you are learning how DBAs gather evidence.

SELECT TOP (25)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Save output to:
C:\DBA\Baselines\InstanceConfig\waits_tempdb_context_before_YYYYMMDD_HHMM.txt

6.2 tempdb space usage (what is consuming tempdb?)

USE tempdb;
GO

SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb,
    SUM(mixed_extent_page_count) * 8 / 1024 AS mixed_extents_mb
FROM sys.dm_db_file_space_usage;

Save output to:
C:\DBA\Baselines\InstanceConfig\tempdb_space_usage_before_YYYYMMDD_HHMM.txt

7) Best Practices: Sizing and Growth (What You Set and Why)

The goal is to reduce autogrowth events and ensure growth is predictable.
A professional approach:

• Pre-size tempdb data files to a reasonable starting size for your lab
• Keep data files equal size (same size, same growth)
• Use fixed growth increments (e.g., 256MB/512MB/1GB depending on scale)
• Configure tempdb log growth as fixed (logs still zero-initialize)

DBA warning: Percent growth on tempdb is risky for the same reason as user DBs,
but the impact is often worse because tempdb sees constant churn.

8) Contention Reduction (Modern SQL Server Behavior + DBA Actions)

tempdb contention historically often involved allocation structures. Modern SQL Server versions include improvements
(for example, changes to reduce allocation bottlenecks), but DBAs still apply standard practices because:

• not all workloads behave the same
• bad file configuration still causes uneven allocations
• I/O placement and growth settings still matter
• tempdb spills still happen under memory pressure

Your DBA actions that reduce contention:

• multiple equal-sized data files
• pre-sizing to reduce frequent growth events
• fixed growth increments
• keeping tempdb on fast storage (conceptually; in lab you may not control this)

9) Making the Change (Lab Exercise): Add/Standardize tempdb Files

In production, you would plan this carefully because it usually requires a SQL Server restart to recreate tempdb cleanly.
In your lab, you will practice the correct workflow.

Step A — Capture baseline using section 5 and 6 scripts.

Step B — Decide your target: For your lab, choose a reasonable number of tempdb data files
(for example, 2–4 data files), all equal size and growth.

Step C — Apply changes
(Example below adds a new tempdb data file and standardizes growth.)
You must adjust file paths for your environment.

/*
LAB EXAMPLE:
1) Standardize tempdb primary data file growth
2) Add an additional tempdb data file
3) Standardize tempdb log growth

Adjust:
- file names
- file paths
- initial size and growth increments
*/

-- 1) Standardize existing tempdb data file (logical name usually tempdev)
ALTER DATABASE tempdb
MODIFY FILE ( NAME = N'tempdev', SIZE = 1024MB, FILEGROWTH = 256MB );

-- 2) Add a second tempdb data file (logical name tempdev2 - you choose)
ALTER DATABASE tempdb
ADD FILE (
    NAME = N'tempdev2',
    FILENAME = N'C:\SQLData\tempdb2.ndf',
    SIZE = 1024MB,
    FILEGROWTH = 256MB
);

-- 3) Standardize tempdb log file growth (logical name usually templog)
ALTER DATABASE tempdb
MODIFY FILE ( NAME = N'templog', SIZE = 512MB, FILEGROWTH = 256MB );

Restart note: tempdb is recreated on SQL Server startup.
Some tempdb changes are best validated after a restart to confirm everything comes up cleanly.
In your lab, you can restart SQL Server service after documenting your change.

10) Validate and Capture “After” Evidence

After changes (and after a restart if you choose to do one), re-run the tempdb file audit and space usage scripts.

Save outputs to:

• C:\DBA\Baselines\InstanceConfig\tempdb_files_after_YYYYMMDD_HHMM.txt
• C:\DBA\Baselines\InstanceConfig\tempdb_space_usage_after_YYYYMMDD_HHMM.txt

Practice (Required)

1. Capture baseline tempdb configuration:
   tempdb_files_before_YYYYMMDD_HHMM.txt
2. Capture baseline tempdb usage:
   tempdb_space_usage_before_YYYYMMDD_HHMM.txt
3. Create a change note:
   C:\DBA\Logs\Changes\change_note_tempdb_YYYYMMDD_HHMM.txt
   (Include rollback: remove added file if needed, revert growth settings, and restart.)
4. Add at least one additional tempdb data file in your lab (or standardize existing ones),
   keeping sizes and growth consistent.
5. Re-run the audit scripts and save “after” snapshots:
   tempdb_files_after_YYYYMMDD_HHMM.txt,
   tempdb_space_usage_after_YYYYMMDD_HHMM.txt
6. In your DBA notebook, answer:
   • List three common operations that use tempdb.
   • Why must tempdb data files be equal size?
   • Why is percent growth risky for tempdb?
   • Does IFI help tempdb log growth? Why or why not?

Lesson Summary

• tempdb is SQL Server’s shared workspace; poor tempdb configuration can slow the entire instance.
• DBAs reduce contention by using multiple equal-sized data files, pre-sizing, and fixed growth increments.
• Evidence-driven tuning means you baseline tempdb layout and usage before changes and validate after.
• tempdb log still requires zero-initialization; growth must be controlled and monitored.

Next Lesson: SQL Server Agent Basics (jobs, operators, schedules)

Next, you will learn SQL Server Agent like a real DBA: how scheduled jobs run,
how to build reliable schedules, how to notify operators, and how DBAs use Agent to prevent outages
(backups, integrity checks, maintenance, and monitoring tasks).