I'm seeing the following tempdb I/O behavior over a one hour period:
There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ~280GB of memory allocated to SQL. One aspect that is interesting is that so much of the I/O is concentrated on the spinning disk drives (E) rather than the solid state drives (F and G) that are handling I/O much more efficiently.
We have pre-allocated the full 300GB each (600GB total) on the F and G drives to tempdb (using 12 files), and we have pre-allocated 1.3TB to tempdb on the E drive (currently 1 file). The I/O data above suggests that tempdb usage is distributed across files based on the current size of the file. I wasn't able to find documentation on this, but I also ran a query like the following to investigate further:
-- While running this query, writes to tempdb are distributed to E/F/G drives
-- in proportion to their current size. This was shown by both
-- sys.dm_io_virtual_file_stats and the space used on the tempdb files before and after
SELECT TOP 100000000 *
The ideal behavior would be that F and G are used exclusively unless they are both full, in which case the spinning disk drive should provide additional tempdb space so that occasional very large workloads can be serviced without running out of tempdb space.
Are we on the right track in observing that tempdb usage is distributed across files based on the current size of the files? It was a little bit surprising to see this type of distribution rather than to see an equal usage of each file (which likely was the assumption of whoever set up this hardware and decided to allocate just one to tempdb on the spinning disk).
Based on Paul White's comment in response to this question, we are thinking about the following approach:
- Shrink the tempdb files on spinning disk. Based on our inital testing, this should shift the current distribution of work more to the solid state drives
- Configure the solid state tempdb files to pre-allocate their space (as we already do now)
- Configure the spinning disk tempdb files to start with no allocation. Make sure that instant file initialization is on. Tempdb will grow on the spinning disk only as needed (which will likely be at most once per week).
- Create a maintenance plan that shrinks the tempdb files on spinning disk after times of peak load, putting the distribution back in favor of the solid state tempdb files.
Does this seem reasonable? Are there any alternative approaches or potential problems to consider? We will obviously test the approach to the extent possible, but won't be able to do so on completely equivalent testing hardware.