TempDB configuration red flags we see every month
Uneven files, tiny autogrowth, and contention patterns that make TempDB the hidden bottleneck on busy systems.
TempDB is shared by almost everything: sorts, hashes, version store, spills, snapshots, temporary objects. When it is misconfigured, the whole instance feels slow.
Red flags from health checks
- A single data file on a multi-core, high-concurrency system
- Multiple files of unequal size (one giant, several tiny)
- Autogrowth set to 1MB or 10% on multi-GB files
- TempDB on the slowest storage tier
- No monitoring of free space or version-store growth
- Trace of repeated autogrowth during peak hours
What “healthier” usually means
- Multiple equally sized data files (count tuned to workload, not dogma)
- Pre-sized files that reduce growth events
- Fixed MB growth increments
- Fast storage with capacity headroom
- Attention to spills and long-running transactions that bloat version store
How this connects to performance work
Wait stats often point at TempDB allocation or I/O under load. Fixing queries without fixing TempDB layout is half a job. Fixing layout without understanding spills is the other half.
A health check identifies the configuration risks; a tuning engagement proves which ones hurt your workload.
Need hands-on SQL Server help?
Allay Data Solutions offers maintenance, tuning, and assessments from Port Elizabeth, South Africa.