sql server hub / tempdb guide

SQL Servertempdb guide

Tempdb is shared engine infrastructure. File layout matters, but so do workload shape, row versioning, spills, waits, storage latency, and growth behavior.

Use this guide when tempdb growth, contention, version store, heavy internal objects, or slow maintenance needs a clean technical check before anyone changes settings.

file layout

space usage

version store

Guide

Guide~10 min readUpdated 19 Apr 2026

LinkedIn X Email

Diagnosis path

Turn tempdb pressure into a next check

10 checks

01What tempdb is used for
02Check tempdb files and growth settings
03Read current tempdb space usage
04Find sessions and tasks using tempdb
05Check version store pressure
06Look for spills and worktable pressure
07Check tempdb contention and waits
08Review storage latency and growth risk
09What not to change blindly
10When to request a health audit

Use this when

Tempdb is visible, but the driver is not clear yet

Check files, growth settings, and current space before changing anything.

Separate user objects, internal objects, and version store usage.

Tie tempdb pressure back to sessions, tasks, waits, plans, jobs, or isolation choices.

Treat tempdb changes as production changes that need timing and rollback.

1 / Tempdb basics

What tempdb is used for

Tempdb is a shared SQL Server workspace. It holds user-created temporary objects, internal worktables, work files for operations such as sorts and hash joins, row-version store data, cursor work, and other engine activity that does not belong in a user database.

That makes tempdb both configuration and workload. File count, size, growth, and storage path matter, but the pressure usually comes from sessions, queries, isolation choices, jobs, maintenance, spills, or concurrency patterns.

Layer	What it answers
Files and growth	Whether tempdb has predictable size, paths, and growth behavior.
Space usage	Whether pressure is user objects, internal objects, version store, or free-space shortage.
Sessions and tasks	Which live work is allocating space in tempdb.
Waits and latency	Whether tempdb pressure is showing up as contention or storage delay.

2 / Files

Check tempdb files and growth settings

Start with the current file layout. Check data files and log file separately, then review whether the data files are equal enough, sized for the workload, and growing in controlled increments.

A file-count change should be based on concurrency and contention signals, not a generic rule. Growth settings should be boring enough that tempdb expansion does not become part of an incident.

Tempdb file layout

Lists tempdb file type, size, growth setting, max size, and physical path.

SELECT
    df.file_id,
    df.name AS logical_name,
    df.type_desc,
    df.physical_name,
    CONVERT(decimal(18,2), df.size / 128.0) AS size_mb,
    CASE
        WHEN df.is_percent_growth = 1
            THEN CONCAT(df.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), df.growth / 128.0), N' MB')
    END AS growth_setting,
    CASE
        WHEN df.max_size = -1 THEN N'Unlimited'
        WHEN df.max_size = 0 THEN N'No growth'
        ELSE CONCAT(CONVERT(decimal(18,2), df.max_size / 128.0), N' MB')
    END AS max_size,
    df.is_percent_growth
FROM tempdb.sys.database_files AS df
ORDER BY df.type_desc, df.file_id;

Data files should usually be reviewed for equal sizing and sensible growth. Percent growth can create unpredictable growth increments as files become large. Physical paths show whether tempdb is on the expected storage. A file layout mismatch is a review input, not an automatic change.

3 / Space usage

Read current tempdb space usage

Current space usage tells you which bucket is consuming tempdb now. User objects, internal objects, and version store point to different next checks.

Free space alone does not prove health. A system can have free space and still be suffering from allocation contention, spills, bad growth behavior, or slow tempdb storage.

Current tempdb space usage

Reads tempdb file space split into free space, user objects, internal objects, version store, and mixed extents.

USE tempdb;

SELECT
    df.file_id,
    df.name AS file_name,
    CONVERT(decimal(18,2), fs.total_page_count / 128.0) AS total_mb,
    CONVERT(decimal(18,2), fs.unallocated_extent_page_count / 128.0) AS free_mb,
    CONVERT(decimal(18,2), fs.user_object_reserved_page_count / 128.0) AS user_objects_mb,
    CONVERT(decimal(18,2), fs.internal_object_reserved_page_count / 128.0) AS internal_objects_mb,
    CONVERT(decimal(18,2), fs.version_store_reserved_page_count / 128.0) AS version_store_mb,
    CONVERT(decimal(18,2), fs.mixed_extent_page_count / 128.0) AS mixed_extents_mb
FROM sys.dm_db_file_space_usage AS fs
JOIN sys.database_files AS df
    ON df.file_id = fs.file_id
ORDER BY df.file_id;

user_objects_mb points toward temp tables, table variables, or explicit tempdb objects. internal_objects_mb points toward worktables, sort/hash work files, and internal engine work. version_store_mb points toward row versioning, snapshot behavior, or long transactions. Uneven file usage can point back to file layout or growth history.

4 / Consumers

Find sessions and tasks using tempdb

Session and task usage help connect tempdb consumption to real work. Session totals are useful for active connections, while task-level rows help when one request is currently allocating heavily.

These counters are best read during the problem window. After a request finishes, some task detail may disappear and the useful link back to query text can be lost.

Session-level tempdb usage

Shows tempdb allocations by session with host, application, login, status, and transaction count.

USE tempdb;

SELECT TOP (50)
    ssu.session_id,
    es.status,
    es.host_name,
    es.program_name,
    es.login_name,
    CONVERT(decimal(18,2),
        (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) / 128.0
    ) AS user_objects_mb,
    CONVERT(decimal(18,2),
        (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) / 128.0
    ) AS internal_objects_mb,
    es.open_transaction_count,
    es.last_request_start_time,
    es.last_request_end_time
FROM sys.dm_db_session_space_usage AS ssu
LEFT JOIN sys.dm_exec_sessions AS es
    ON es.session_id = ssu.session_id
WHERE ssu.session_id <> @@SPID
ORDER BY
    (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count)
  + (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;

user_objects_mb points toward temp tables or explicit tempdb object use. internal_objects_mb points toward sort, hash, cursor, or worktable activity. program_name and host_name connect the usage to an application or job runner. Open transactions can keep work alive longer than expected.

Task and request tempdb usage

Shows task-level tempdb usage with active request status, waits, blocker, and running statement.

USE tempdb;

SELECT TOP (50)
    tsu.session_id,
    tsu.request_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    CONVERT(decimal(18,2),
        (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) / 128.0
    ) AS task_user_objects_mb,
    CONVERT(decimal(18,2),
        (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) / 128.0
    ) AS task_internal_objects_mb,
    SUBSTRING(
        sql_text.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(max), sql_text.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS running_statement
FROM sys.dm_db_task_space_usage AS tsu
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = tsu.session_id
   AND r.request_id = tsu.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE tsu.session_id <> @@SPID
ORDER BY
    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)
  + (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC;

task_internal_objects_mb is useful during active spill or worktable pressure. running_statement gives the next query or plan to inspect. wait_type and blocking_session_id help separate tempdb use from blocking. If one request dominates, check its plan, memory grant, and recent changes.

5 / Version store

Check version store pressure

Row-versioning features can reduce reader/writer conflicts, but they move work into tempdb. Snapshot isolation, read committed snapshot, online operations, and long-running transactions can all make version store space grow.

Version store pressure should be checked by database, then tied back to transaction length, isolation settings, workload timing, and cleanup behavior.

Version-store usage by database

Shows how much tempdb space version-store records use for each database.

SELECT
    DB_NAME(vs.database_id) AS database_name,
    CONVERT(decimal(18,2), vs.reserved_page_count / 128.0) AS version_store_mb
FROM sys.dm_tran_version_store_space_usage AS vs
ORDER BY vs.reserved_page_count DESC;

version_store_mb shows which database is contributing version records. Rapid growth points toward row-versioning workload or long transactions. Low values are normal on systems not using much row versioning. If this grows during incidents, check active transactions and isolation choices next.

6 / Worktables

Look for spills and worktable pressure

Internal-object growth often means SQL Server is using tempdb for worktables, work files, sorts, hashes, cursors, or other execution work. That can be normal, but repeated pressure usually needs query-plan review.

Do not blame tempdb first. Check memory grants, estimates, statistics, indexes, row counts, and whether one report, ETL step, or maintenance job is pushing the same expensive shape into tempdb repeatedly.

Plan checks

Look for sort, hash, or exchange operators that spill during the slow window.

Check whether estimates are far from actual row counts.

Review memory grants before changing tempdb files.

Use the indexing guide when a poor access path is widening tempdb work.

7 / Contention

Check tempdb contention and waits

Tempdb allocation contention often appears as `PAGELATCH` waits against tempdb resources. This is different from storage I/O waits. It is latch contention on in-memory allocation pages, often under high temp object churn or concurrent workload pressure.

The fix depends on the pattern. File layout can matter, but so can query behavior, temp table churn, object design, workload timing, and SQL Server version.

Active waits pointing at tempdb

Shows active waiting tasks and requests where the wait resource or database points toward tempdb.

SELECT
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    r.status,
    r.command,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = wt.session_id
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = wt.session_id
WHERE wt.session_id <> @@SPID
  AND (
      wt.resource_description LIKE N'2:%'
      OR r.wait_resource LIKE N'2:%'
      OR r.database_id = 2
      OR wt.wait_type LIKE N'PAGELATCH%'
  )
ORDER BY wt.wait_duration_ms DESC;

PAGELATCH waits with tempdb resources can point to allocation contention. wait_duration_ms shows whether the wait is momentary or building up. program_name and host_name help connect waits to a workload source. If blockers appear, continue with the blocking guide before changing tempdb.

8 / Storage and timing

Review storage latency and growth risk

Tempdb pressure can be allocation contention, space pressure, or storage latency. File I/O stats help decide whether tempdb reads and writes are waiting on the storage path.

Job timing matters too. Maintenance, reporting, ETL, index work, and large loads can make tempdb look like the problem when the real issue is workload overlap.

Tempdb file latency

Reads read/write counts, stalls, and average latency for tempdb files.

SELECT
    mf.file_id,
    mf.name AS logical_name,
    mf.type_desc,
    vfs.num_of_reads,
    vfs.num_of_bytes_read,
    vfs.io_stall_read_ms,
    CONVERT(decimal(18,2),
        vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0.0)
    ) AS avg_read_latency_ms,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms,
    CONVERT(decimal(18,2),
        vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0.0)
    ) AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) AS vfs
JOIN tempdb.sys.database_files AS mf
    ON mf.file_id = vfs.file_id
ORDER BY avg_write_latency_ms DESC, avg_read_latency_ms DESC;

avg_write_latency_ms matters because tempdb-heavy work can be write-sensitive. High stalls during pressure windows deserve storage and workload timing review. Compare data and log files separately. Latency numbers need context from monitoring, not one isolated snapshot.

SQL Agent job timing

Checks recent SQL Agent job steps when tempdb pressure may align with maintenance, reporting, or ETL work.

SELECT TOP (50)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS run_duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.step_id > 0
ORDER BY run_start_time DESC;

run_start_time lets you compare jobs with tempdb growth or wait spikes. run_duration_seconds helps find jobs overlapping the slow period. Long or failed steps may explain sudden tempdb pressure. If a job aligns with the incident, check its query plans, memory grants, and tempdb usage.

9 / Do not guess

What not to change blindly

Tempdb changes can require restarts, storage moves, workload timing, and rollback planning. Treat them as production changes, not small cosmetic cleanup.

Do not change tempdb file count from a generic rule alone.

Do not shrink tempdb as a routine fix.

Do not treat free space as proof that tempdb is healthy.

Do not move tempdb or change growth settings without a rollback and restart plan.

Do not enable row-versioning isolation without watching version-store growth.

Do not blame tempdb for spills before checking query plans, memory grants, statistics, and indexes.

Do not rebuild indexes or run maintenance during a tempdb pressure incident unless the job is clearly the cause and can be safely stopped.

Do not ignore PAGELATCH waits pointing at tempdb allocation pages.

10 / Health audit

When to request a health audit

A health audit makes sense when tempdb pressure is real but mixed with broader environment questions: old file settings, weak monitoring, storage concern, blocking, maintenance overlap, row-versioning changes, or repeated slow periods.

Send tempdb file settings, current space usage, version-store usage, session/task usage during pressure, waits, file latency, job timing, monitoring data, and what changed recently.

Request health audit Read the waits guide

Next step

If tempdb is part of a wider environment problem, use the SQL Server health audit page or request the audit above.

Next useful reads: the SQL Server health check guide for full-environment review, the SQL Server blocking guide for lock chains, the SQL Server indexing guide for plan and spill review, and the SQL Server monitoring guide for better recurring capture.