SQL Server tempdb Configuration Best Practices

The first thing I open on any new instance is tempdb, because everyone shares it. Spilled sorts, hash joins, temp tables, table variables, the snapshot version store and online index rebuilds all land there. A bad setup shows up as PAGELATCH waits on a few allocation pages, or one file creeping up in tiny steps on a slow disk. It's recreated from model at each restart, so the fix is cheap: one restart.

This is my checklist for SQL Server 2012 through 2016.

Look at the current layout

SELECT name, physical_name, size * 8 / 1024 AS size_mb,
       CASE WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(10)) + '%'
            ELSE CAST(growth * 8 / 1024 AS varchar(10)) + ' MB' END AS growth
FROM tempdb.sys.database_files;

The default from a pre-2016 install is one data file of 8 MB and one log file of 1 MB, both with 10 percent autogrowth, sitting in the instance data directory. That is the configuration to get away from.

More data files

The reason for multiple files is contention on the allocation bitmap pages (PFS, GAM, SGAM), not I/O throughput. With one file, every session creating a temp table fights over the same pages. You can see it as waits like this:

SELECT session_id, wait_type, wait_resource
FROM sys.dm_exec_requests
WHERE wait_type LIKE 'PAGELATCH%' AND wait_resource LIKE '2:%';

2:1:1 is the PFS page of file 1 in database 2 (tempdb); 2:1:3 is the SGAM page.

The rule Microsoft has published for years: one data file per logical core, up to eight. Add more only if contention remains. I once made one file bigger "to be safe" and undid all the benefit. All files must be the same size with the same growth, because the proportional fill algorithm favours the file with the most free space and a bigger file will attract all the allocations.

USE master;
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 2048MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'T:\TempDB\tempdev2.ndf', SIZE = 2048MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'T:\TempDB\tempdev3.ndf', SIZE = 2048MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'T:\TempDB\tempdev4.ndf', SIZE = 2048MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 4096MB, FILEGROWTH = 512MB);

The SQL Server 2016 installer does this for you on the TempDB tab of Database Engine Configuration, which is the main reason I no longer see a single-file tempdb on new 2016 servers.

Put them on a fast disk

tempdb does not need to survive a crash, so it is the ideal candidate for a local SSD even when the user databases live on a SAN. Moving is a MODIFY FILE with a new path followed by a restart; the old files can be deleted afterwards.

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'T:\TempDB\tempdev.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'T:\TempDB\templog.ldf');

Make sure the SQL Server service account has Full Control on T:\TempDB before restarting, or the service will not start.

Trace flags 1117 and 1118 (pre-2016 only)

On SQL Server 2014 and earlier, two trace flags belong in the startup parameters:

• -T1118 makes all allocations use full extents instead of mixed extents, which reduces SGAM contention.
• -T1117 makes all files in a filegroup grow together, keeping them the same size.

Add them in SQL Server Configuration Manager under Startup Parameters, or with DBCC TRACEON (1117, 1118, -1) until the next restart. In SQL Server 2016 both behaviours are built in for tempdb (ALTER DATABASE ... SET MIXED_PAGE_ALLOCATION OFF and AUTOGROW_ALL_FILES are the per-database equivalents), and the trace flags have no effect.

Presize it

Size the files to what tempdb reaches at the busiest time, not the 8 MB default. If you don't know yet, I watch it for a week:

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

Because tempdb is recreated at startup, the sizes you set with MODIFY FILE are the sizes it starts with every time. Autogrowth should be a safety net with a fixed increment, never the normal way the files reach their working size.

Restart and check

Restart the instance and confirm the layout stuck:

SELECT name, physical_name, size * 8 / 1024 AS size_mb FROM tempdb.sys.database_files;

All data files should show the same size and the new path. Then run a workload and watch sys.dm_os_waiting_tasks for PAGELATCH_UP or PAGELATCH_EX on resource 2:x:1 or 2:x:3. If they are gone or spread across files, contention is solved.

Notes from things going wrong

• "Could not create tempdb. You may not have enough disk space available." together with the service refusing to start after a move. The new folder does not exist or the service account cannot write to it. Start the instance with -f (minimal configuration) from the command line, fix the path with MODIFY FILE, and restart normally.
• "The database 'tempdb' cannot be opened. It is in the middle of a restore." is not about tempdb itself; it is usually error text from an application after the instance was restarted mid-transaction. Just retry.
• Adding files does not help because one file is much larger than the others. Proportional fill sends new pages to the file with the most free space. Shrink the big one or grow the others so they match, then restart.
• tempdb log fills up ("The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'"). A long-running query with a big sort or a snapshot transaction is holding the version store. Find it with sys.dm_tran_active_snapshot_database_transactions and check tempdb_usage in sys.dm_db_session_space_usage.

Equal-sized files, fast local storage, presized. That's the whole recipe, and 2016 setup now does most of it for you.