TempDB files too small.
Why TempDB Files Smaller Than 1 MB Are a Bad Idea
Creating TempDB data files that are tiny (e.g., 512 KB, 256 KB, or even 1 MB) is surprisingly common when administrators add many files “just in case” without considering initial size. This practice almost always causes more problems than it solves.
1. Instant and Frequent Autogrowth Events
SQL Server allocates new extents (8 pages = 64 KB) round-robin across all TempDB files. With very small files, each file fills up after only a handful of extents and immediately triggers autogrowth. Result: hundreds or thousands of tiny growth events per minute instead of occasional large ones.
2. Version Store Blowups Become Expensive
Queries that generate version records (triggers, online index rebuilds, MARS, RCSI, etc.) write to TempDB. Every time a file grows, SQL Server must zero out the new space (unless Instant File Initialization is enabled). Hundreds of small growths = hundreds of zeroing operations = massive I/O spikes and latency.
3. Severe Logical Fragmentation Inside the Files
Small files mean SQL Server frequently runs out of mixed extents and must allocate new uniform extents. The allocation bitmaps (GAM, SGAM, PFS) become extremely fragmented, making every new allocation slower.
4. Proportional Fill Becomes Meaningless
SQL Server’s proportional fill algorithm tries to keep all files roughly the same size. If files start at 512 KB and grow in large increments, the algorithm spends more time calculating ratios than doing useful work.
5. Startup and Recovery Time Increases
At every SQL Server restart, TempDB is recreated. Creating dozens of tiny files adds unnecessary overhead for file header initialization, boot page writes, and zeroing (when IFI is not enabled).
Real-World Example of TempDB files
A customer had 64 TempDB files configured at 1 MB initial size with 10% autogrowth. During a heavy batch window they experienced:
- 600–800 file growth events per minute
- Average write latency on TempDB drive > 100 ms
- High SIGNAL_WAIT and WRITELOG waits
After switching to 8 files of 8 GB each (same total size), file growths dropped to approximately 2 per hour and write latency fell to < 2 ms.
Recommended Minimum Sizes (2025 Best Practice)
| Workload Intensity | Minimum Initial Size per File | Typical Autogrowth |
|---|---|---|
| Light / Dev / Test | 100–500 MB | 256–512 MB |
| Moderate OLTP | 1–4 GB | 512 MB–1 GB |
| Heavy OLTP / DW / Reporting | 4–16 GB | 1–4 GB |
Bottom Line
Never create TempDB files smaller than 100 MB, and in production you should almost always start in the multiple-gigabyte range per file. Small files do not give you “finer-grained” allocation control — they give you allocation chaos. Size your TempDB files so they grow a few times per month at most, not thousands of times per hour.