SQL Server TempDB Best Practices: Configuration and Troubleshooting

Every SQL Server instance has one tempdb, and every database on that instance shares it. When tempdb is slow or full, everything slows down or fails at once. A few configuration choices and monitoring habits prevent most tempdb problems.

SQL Server tempdb best practices

What uses tempdb

  • Your objects: #temp tables and table variables.
  • Internal work: sorts, hash joins and aggregations that spill out of memory, spools, and some index operations such as SORT_IN_TEMPDB.
  • The version store: row versions for read committed snapshot isolation (RCSI), snapshot isolation, online index rebuilds and triggers. (With Accelerated Database Recovery, versions are kept in the user database instead.)

tempdb is recreated empty every time SQL Server restarts.

Use multiple, equally sized data files

With a single data file, many sessions creating temp objects at once compete for the same allocation pages, which shows up as PAGELATCH_* waits on tempdb pages. Multiple data files spread that work out. The widely used starting point is:

  • One data file per logical CPU core, up to 8 files.
  • If contention remains with 8, add more in groups of 4.
  • Keep all data files the same size with the same growth setting, so SQL Server spreads allocations evenly across them.

Since SQL Server 2016, setup suggests multiple files automatically, and the old trace flags 1117 and 1118 are no longer needed for tempdb because that behaviour is now the default.

Pre-size it and use fixed growth

Because tempdb is recreated at startup, an undersized tempdb grows again and again after every restart, and growth events pause activity. Size it for your normal peak usage up front, and set autogrowth in fixed megabytes rather than percentages:

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE = 8192MB, FILEGROWTH = 512MB);
-- repeat for each data file (temp2, temp3, ...), keeping sizes identical

Instant file initialization (granting the SQL Server service account the "Perform volume maintenance tasks" right) makes data file growth much faster. It does not apply to log files.

Put it on fast storage

tempdb is write-heavy and does not need to survive a restart, so it is ideal for the fastest local SSD or NVMe storage you have. On Azure virtual machines, many teams place tempdb on the local temporary disk for low latency, taking care that the folder exists at startup.

Spot allocation contention

SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE '2:%';   -- database_id 2 = tempdb

Frequent results here, especially on pages like 2:1:1, 2:1:2 or 2:1:3, point to allocation contention: add data files if you have fewer than eight.

Memory-optimized tempdb metadata

From SQL Server 2019, contention on tempdb's system tables in very busy systems can be reduced by moving tempdb metadata into memory-optimized tables. It needs a restart and has some limitations, so enable it when you have confirmed metadata contention:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Find out who is using the space

-- Overall breakdown
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_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- Sessions using the most
SELECT TOP (10) session_id,
    (user_objects_alloc_page_count + internal_objects_alloc_page_count) * 8 / 1024 AS allocated_mb
FROM sys.dm_db_session_space_usage
ORDER BY allocated_mb DESC;

Watch the version store

If you use RCSI or snapshot isolation, a single long-running transaction keeps old row versions alive, and the version store can grow until tempdb fills. When version_store_mb keeps rising, look for the oldest open transaction (for example in sys.dm_tran_active_snapshot_database_transactions) and deal with it.

Azure SQL Database

In Azure SQL Database, tempdb is managed for you; its size depends on the service tier. You still benefit from reducing spills and avoiding huge temp tables, but file layout is not something you configure.

Quick checklist

  • Up to 8 equally sized data files to start.
  • Pre-size for peak use; fixed-size autogrowth.
  • Fast local storage.
  • Monitor PAGELATCH waits on database 2.
  • Track space by type and by session, and watch the version store.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more