My post from last week sparked quite a bit of discussion at work about how the data files for TempDB fill up when they are not of equal size. Pretty much all of the documentation and reccomendations out there say to keep the data files the same size so that the round-robin data flow works properly. What we were having trouble deciding is what happens when they are not the same size, for example if you have one file that is 5GB and three files that were 500 MB.
The discussions ended up in two different camps.
TempDB would allocate pages in the first larger file until the free space was equal to the three smaller files then the round robin would continue.
The allocations would still be handled in a round robin fashion with the larger file just getting more so that they would fill up evenly (from a percentage standpoint).
Like any good discussion we decided to take this to our test boxes and see if we could figure it out The answer I found comes down to a little guy named proportional fill.
As you can see, the allocations were made to all of the files, not just the new one. It is just that more allocations were made to the new 500 MB file than to the old 500MB files during this second test (361MB as opposed to about 192 MB). Proportional Fill strikes again! If you look at how the numbers are trending you can see that if I hadn't set a limit on my while loop all of the files would have been almost equally full when the first one completely filled up.
He uses extended events to show this so you can actually see the number of file writes per data file. His test is not specifically on TempDB but the allocation should work the same way.
I'll be back in two weeks. Hopefully I can squeeze one more blog post out of all my TempDB testing.