SQL Server TempDB – Number of Files – The Raw Truth

I continue to answer questions about the number of TEMPDB files and trace flag –T1118. It seems there are plenty of advice blogs, wikis, articles and other resources. To help clarify this I am going to post my latest e-mail exchange.

It is not just SGAM in SQL 2000 that encountered contention. SQL Server can still heat up the PFS and other allocation pages on SQL 2005 and 2008 under very heavy use conditions. The temp table caching added to SQL 2005 has helped but on larger systems we can still get issues when the limits are pushed.

-T1118 forces uniform extent allocations so the answer is yes, it does help. Each data files is tracked with a file control block (FCB). The FCB contains members for the last uniform extent allocation and last mixed page allocation. So what happens during a new allocation is a call to the file group manager. The file group manager determines the next file to allocate from and from there the file control block reports the targeted starting point for free space searching based on the request type (mixed or uniform).

By adding a file per CPU what you are doing is allowing the file group manager to pick the next file (there are some optimizations here for tempdb to avoid contention and skip to the next file when under contention) and then use the target location to start locating free space.

-T1118 avoids all the logic to determine if a mixed extent is available or needs to be allocated and avoids the SGAM allocation patterns. So when the same file does have a collision a uniform extent is used and any mixed extent allocation contention on the SGAM associated allocation pages can be avoided.

One file per CPU that SQL Server sees. SQL Server creates a logical scheduler for each of the CPUs presented to it (logical or physical). The reason for the multi-file TEMPDB recommendation is to allow each of the logical schedulers to loosely align with a file. Since you can only have 1 active worker per scheduler this allows each worker to have its own tempdb file (at that instant) and avoid allocation contention.

8 is not a limit for TEMPDB we have many deployments with more than 8 TEMPDB files. 8 is the general cap for MAXDOP setting.

you make a comment about maxdop, is any of the tempdb files affected by the server parallelism setting at all. e.g. if parallelism is turned off on the server is 1 file per cpu still a good idea when tempdb use is heavy?