Is SQL Server's fill strategy the same for multi-file tempdb as multi-file (non-tempdb) filegroups?

Question

Will tempdb I/O from a single query be split across multiple tempdb files? (assuming that tempdb is configured to use multiple files, of course!)

For non-tempdb databases, MDSN seems to say that yes, newly-added data will be spread across multiple files in a filegroup:

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Does this same fill strategy apply to tempdb?

And does this answer depend on the type of query, e.g. parallel vs. non-parallel? Or is the answer different based on the kind of tempdb I/O, e.g. for temp ables I create vs. tempdb usage by the database engine for worktables or spilling?

Answer 1

Does this same fill strategy apply to tempdb?

Yes it does.

To prove it to yourself you can do a test similar to one described here:

SQL Server Proportional Fill Algorithm Example

i.e. populate temp table with some amount of data and check space used for every tempdb file.

I cite here only the conclusion:

File groups use proportional fill strategy across all the files within each file group and uses a round robin algorithm to write a proportional amount to the free space in the file and to each file within the file group.

Understanding the proportional fill and round robin behavior in SQL Server is important not only for user databases, but especially in the case of tempdb as well. The SQL Server best practice is to have multiple tempdb data files to alleviate tempdb PFS and SGAM contention. It is easy to imagine when the tempdb data files sizes are uneven, then the extent allocations would be heavier on the bigger sized data files. This configuration would again cause a hotspot and not alleviate tempdb contention.

And here is a further reading: Correctly adding data files to tempdb by Paul Randal

Among other things, Paul describes how to add files correctly to match existing file sizes, and the reason of doing so is proportional fill algorithm.