TempDB with excessive files
How Many TempDB Files Are Too excessive in SQL Server?
One of the most common TempDB performance questions is: “How many data files should I create for TempDB?” The immediate follow-up is almost always: “OK, but how many is too many?”
There is no single magic number that applies to every workload, but there are clear guidelines and practical limits where adding more files stops helping and can even hurt.
Microsoft’s Official Recommendation
Microsoft recommends starting with one TempDB data file per logical CPU core, up to a maximum of 8 files, and then only adding more if contention persists.
- If you have ≤ 8 logical cores → create as many files as cores
- If you have > 8 logical cores → start with 8 files and add more in increments of 4 only if you still see PAGELATCH_EX or PAGELATCH_SH waits on PFS, SGAM, or GAM pages in TempDB.
This guidance has been consistent since SQL Server 2016.
When Do Additional Files Stop Helping and become excessive?
In real-world testing and field experience, most systems see diminishing returns after 8–12 files. After that, the benefits drop off sharply:
| Number of Files | Typical Benefit Observed |
|---|---|
| 1 → 4–8 | Huge reduction in allocation contention |
| 8 → 12 | Small to moderate improvement in high-concurrency workloads |
| 12 → 32 | Usually no measurable improvement, sometimes slightly worse |
| 32+ | Almost always negative impact |
Why Too Many Files Can Hurt Performance
- More files = more allocation bitmaps to scan
Every time a new page is needed, SQL Server checks SGAM/PFS in every file. With dozens of files this overhead adds up. - Proportional fill algorithm overhead
SQL Server tries to keep all files the same size — more files means more bookkeeping. - Increased chance of uneven growth
Autogrowth events across 32+ files can cause short-term stalls and fragmentation. - Backup/restore and CHECKDB overhead
Every TempDB file is opened at startup and checked during DBCC CHECKDB. - Memory pressure on SGAM/PFS caching
Each file has its own allocation pages that need to stay in memory.
Real-World Observations from the Field
- The vast majority of systems that had 32, 48, or 64 TempDB files (usually because someone followed “one file per core” on a 64-core box) saw slightly better performance after reducing to 8–12 files.
- Systems with extremely high concurrent temp table creation (thousands of sessions per second) sometimes benefit from 24 or even 32 files, but this is rare.
- VMware and Azure VMs with high core counts (48–96 vCPUs) almost never need more than 12 files.
Practical Rule of Thumb (2025)
| Logical Cores | Recommended TempDB Files | Notes |
|---|---|---|
| ≤ 8 | Equal to cores | |
| 9–32 | 8 to 12 | Start with 8, add 4 at a time if contention remains |
| 32+ | 12 to 16 | Almost never justified to go higher |
Bottom Line
12 TempDB files is almost always enough. 32 is almost never helpful. Any more almost certainly hurting you.
Configure 8–12 equally-sized files with instant file initialization and identical autogrowth settings, monitor allocation contention waits, and only add more files in groups of 4 if you still see PFS/SGAM/GAM latch waits after that. In 99% of environments, you will never need to go beyond 16.
Resist the temptation to create one file per core on modern high-core-count servers. TempDB scaling is not linear past about 8–12 files.