Blitz Result: Only One TempDB Data File
SQL Server uses a shared workspace database called TempDB to handle a lot of system tasks (as well as user-created temp tables.) This database installs with just one data file and one log file by default. This part of our SQL Server sp_Blitz script checks to see if you’ve increased that number.
For most of the world, one data file is okay, but as your system starts to grow, it may run into an issue called SGAM contention.
How Can I Tell if My TempDB Database Has this Performance Problem?
Learn more in our 30 minute video, Sizing Up the TempDB Database – Best Practices.
Feel like reading instead of watching a video? Check out these posts:
- Beginners: How to configure TempDB by Microsoft PSS
- Intermediate: How to know whether you’re suffering from TempDB contention by Adam Machanic
- Advanced: GAM, SGAM, and PFS internals by Paul Randal
To Fix the Problem
For starters, create three additional TempDB data files, and make them all equally sized with the first data file. If you’re using a dedicated drive for TempDB and you don’t have enough space to create additional files, shrink the first data file to make room for the others.
They should all grow in equal amounts, too – don’t set one of them to grow in 1% increments, and the others to grow by 100MB at a time.
After changing the configuration, restart the SQL Server instance at your next convenient opportunity. If you can’t restart it for a while, hold off on this change until your next scheduled maintenance window.
There is a slight risk: if you’ve got poorly written queries that assume data is ordered – without actually specifying an ORDER BY clause. Kendra Little explains the perils.