TempDB file overflow

Question

This is my experience with TempDB. The Algorithm works better when the TempDB File sizes are the same size.

We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We needed that file larger in the case of run away queries (caused by people not logging off their machines properly - not the SQL Server) which occasionally caused TempDB to fill.

We switched to 16 TempDB files (16 CPU's) on RamDisk all the same size with No Autogrowth.

We setup a job to kill the spid using the most TempDB resources, once TempDB was 80% full.

In working with Microsoft, their technician tested if a TempDB file was created on one TempDB file it would overflow to a second TermpDB file if it needed too (I was concerned about this).

Our SQL Server is a Read-Only AlwaysOn and we run some pretty big reports that use large amounts of TempDB. Our new TempDB design has been in production for over 6 months, with no issues. Occasionally, we have a spid cancelled due to TempDB over 80% full, but not very often.

The question

Curious, is there some SQL code I can use to quickly determine if files are crossing multiple TempDB files? We are on SQL Server 2017 CU 12.

Answer 1

Jonathan's blog post describes quite nicely that the tables are striped across the data files in a filegroup and written to in a consistent manner.

Where the files are the same size and starting from empty, you should see the same amount of data for the table across each file.

You should be able to use the same xEvents scripts to see this for your own system.