Tempdb and I/O misconceptions

Many doubts are always around when the subject is the creation of extra files for tempdb, recently I followed a thread in our Brazilian SQL Server forum, where the subject was debated, and I would like to point a few things out based on my understanding about the subject:

- The myth about SQL Server allocating one thread for each physical file is incorrect. SQL Server has a pool of threads that get divided between the active connections (when there is an active task) and, accordingly with MAXDOP configuration and server pressure, the engine defines how many threads each connection receives. Once the query does an I/O request, the thread will be used to issue a request to the I/O subsystem, so a file can receive requests from many threads at once.

- The addiction of more files to the tempdb helps, a lot, critical environments where usually we have a greater tempdb pressure, due to more batches being processes simultaneously. Note that every object that gets created at tempdb in controlled initially thru the PFS and SGAM pages, if I have a lot of activity in those pages, SQL Server possibly has a contention point. This contention happens in memory because those pages are hot spots and there is no reason they should be written to disk.

When we create more tempdb files (even in the same disk), each file will have its control pages and SQL Server will be able to work with a round robin mechanism between all the files, dividing the pressure among those control pages by the number of files. Note that for this to happen, the files should have similar sizes, otherwise SQL Server will favor bigger sizes, unbalancing the even distribution of work.