When managing SQL Server tempdb, many teams hear that a single data file can simplify configuration and reduce contention. Understanding how this guidance from experts like Brent Ozar fits into broader performance strategy helps you balance simplicity with scalability.
This article explores the recommendation to use only one tempdb data file, how it aligns with Brent Ozar Unlimited resources, and when additional files make sense for workload and hardware characteristics.
| Configuration Option | Description | Impact on tempdb | When to Use |
|---|---|---|---|
| Single Data File | One tempdb data file, typically named tempdev. | Simplifies management, reduces internal contention on low concurrency workloads. | Light to moderate concurrency, quick prototyping, many tempdb usage scenarios. |
| Multiple Data Files | 38;Multiple tempdb data files sized equally, up to one per core guideline on older SQL versions. | Reduces PFS, GAM, SGAM contention, can improve scalability under high allocation activity. | High concurrency, heavy temp table creation, large row versioning workloads. |
| Trace Flag 1118 | Forces uniform extent allocation, often used with multiple tempdb files. | Reduces mixed extent contention, complements multiple-file setups. | Environments with heavy mixed extents historically causing contention. |
| Instant File Initialization | Grants SQL Server permission to skip zeroing data files. | Speeds up tempdb growth and auto-initialization, lowers downtime for file operations. | Environments prioritizing fast tempdb recovery and growth on Windows. |
Understanding Single Tempdb Data File Guidance
The idea of using only one tempdb data file challenges the older myth that multiple files always boost performance. Brent Ozar Unlimited and similar experts clarify that many systems run well with a single file, especially when tempdb usage is moderate.
This approach minimizes internal contention on metadata structures and keeps file management straightforward. Administrators can focus on other tuning areas instead of juggling many tempdb files.
Tempdb File Configuration Best Practices
Start Simple with One File
Begin with a single data file sized appropriately for workload, using a predictable growth increment to avoid unexpected autogrowth events. Monitor wait stats related to allocation pages to confirm no contention appears over time.
Scale When Evidence Appears
If you observe PAGELATCH_UPREDICTOR waits on specific allocation pages or significant delays linked to tempdb allocations, evaluate adding files and evenly sizing them while removing trace flag 1118 if used.
Performance Considerations and Monitoring
When Single File Suffices
Many OLTP workloads, moderate reporting queries, and test environments perform well with one tempdb file because allocation contention is low and simplicity reduces operational risk.
Signs You Need Multiple Files
High concurrency systems with frequent creation and destruction of temp tables, heavy row versioning, or large in-memory operations can benefit from multiple equally sized tempdb data files aligned to logical processors.
Configuration and Maintenance Guidance
- Pre-size the single tempdb data file to avoid frequent autogrowth based on peak workload patterns.
- Use trace flag 1118 only when diagnostics indicate mixed extent contention, and prefer uniform extents with one file per data file.
- Enable instant file initialization on Windows to speed up file growth and tempdb recreation.
- Monitor tempdb wait types such as PFS, GAM, and SGAM to detect allocation contention early.
- Align the number of tempdb data files to workload patterns rather than blindly following core-based formulas.
FAQ
Reader questions
Should I always use only one tempdb data file for every SQL Server instance?
Not always; use one file as a starting point for moderate workloads, and add files when monitoring shows allocation contention under high concurrency.
Does Brent Ozar recommend only one tempdb data file in all cases?
Brent Ozar emphasizes that one file is often sufficient and simpler, but he also supports multiple files when diagnostics indicate contention on allocation pages in high-load systems.
How do I know if my workload needs multiple tempdb files?
Look for PAGELATCH waits on tempdb allocation pages, elevated PFS, GAM, or SGAM waits, and increased tempdb contention during peak workloads to justify adding files.
Can I mix instant file initialization with a single tempdb data file?
Yes, enabling instant file initialization benefits a single tempdb file by speeding up growth and initialization on Windows, reducing downtime during file operations.