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?
Best Answer
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
tempdbfile.I cite here only the conclusion:
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.