Unequally sized tempdb data files

What’s the issue?

Tempdb is typically configured with multiple data files to reduce allocation contention on shared system pages such as PFS, GAM, and SGAM. SQL Server distributes activity across multiple data files using a proportional fill algorithm, where each file receives writes in proportion to its free space relative to the other files in the filegroup.

For the proportional fill algorithm to distribute work evenly, the data files must be sized equally. When the files have different sizes, the algorithm directs more activity to the larger files, defeating the purpose of having multiple files in the first place.

This finding identifies instances where the tempdb data files are not all the same size.

Why is this a problem?

Unequal tempdb file sizes cause the proportional fill algorithm to send disproportionate activity to the largest files. This concentrates allocation requests on a subset of the data files and reintroduces the very contention that having multiple files was supposed to eliminate, including the PAGELATCH waits on allocation pages that are the most common reason to add files in the first place.

The condition often results from autogrowth events that did not apply equally to all files. Without trace flag 1117 (on SQL Server 2014 and earlier) or the AUTOGROW_ALL_FILES filegroup option (on SQL Server 2016 and later), only the file that triggered autogrowth grows, leaving the others at their original size. Over time, repeated autogrowth events on different files can produce significant divergence in file sizes.

Manual changes can also produce the imbalance. When tempdb files are added to an instance, they sometimes get added at different sizes than the existing files, or initial sizing during installation may not have been applied uniformly. Without periodic review, the imbalance persists indefinitely and the team may not realize it is undermining tempdb performance.

What should you do about this?

Identify the current tempdb data file sizes and growth settings by querying sys.master_files for database_id = 2. Capture the size and growth value of each data file and identify the disparities.

Resize the data files to match using ALTER DATABASE tempdb MODIFY FILE (NAME = N’LogicalFileName’, SIZE = );, choosing a target size that fits the workload and can fill most of the dedicated tempdb drive. Apply the same growth setting across all files using FILEGROWTH = so future growths produce consistent results.

Enable AUTOGROW_ALL_FILES on the tempdb primary filegroup using ALTER DATABASE tempdb MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES; (on SQL Server 2016 and later) so future autogrowth events apply to all files together rather than producing imbalance. On SQL Server 2014 and earlier, achieve the same effect using trace flag 1117 as a startup parameter.

Read more…

tempdb Database – SQL Server | Microsoft Learn