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.) Query operations like joins and aggregations happen there. Online index rebuilds and INSTEAD OF triggers are also done in tempdb. Table spools and ORDER BY too. (It’s a busy place — you get the idea.)

Tempdb 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 tempdb data files. (One log file is just fine.)

For most of the world, one data file is okay, but as your system starts to grow, it may run into an issue called page contention on GAM, SGAM, or PFS pages. SQL Server has to write to special system pages to allocate new objects. ‘Latches’ — think of them as lightweight locks — protect these pages in memory.

On a busy SQL Server you can end up waiting a long time to get your ‘latch’ on a system page in tempdb. This can make your queries run very slowly in some cases.

How Can I Tell if My TempDB Database Has this Performance Problem?

SQL Server wait stats are the key to diagnosing tempdb contention. You might see PAGELATCH_UP, PAGELATCH_EX, or PAGELATCH_SH waits in our free sp_BlitzFirst® procedure.

You might also see the issue live if you catch it in action using Adam Machanic’s free sp_WhoIsActive procedure, like this:

It’s OK to be a Little Proactive Here

Don’t go too crazy adding files– you can go too far and that can slow you down. But this is a common enough issue that it’s worth it to size tempdb files proactively and prevent the issue from happening.

How to Fix It: The Short Version

• For the easy button fix, just use eight equally sized tempdb data files. The odds are good that will be enough for most systems.
• If you’re ready for a little rocket science, follow the steps below.

Return to sp_Blitz or Ask Us Questions

---

How to Add tempdb Data Files to Prevent GAM and SGAM Contention

The current guidance from Microsoft in KB 2154845 is to use the same number of tempdb files as the number of logical processors up to 8 logical CPUs. If you have more than 8 logical processors, don’t add more unless you observe you have contention.

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.

Do You Need an Outage to Add tempdb files?

Technically, not always. But practically speaking, yes. If you have to shrink files, that’s very difficult while SQL Server is in use, and we’ve seen cases where anti-virus/file control tools didn’t act up until SQL Server was restarted after the files were added.

There is another 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.

Want to Use TSQL? Here’s a starter script to Modify

Here’s a sample script to get you started. Be sure to customize it to reflect the proper size and file path for your environment:

Why isn’t filegrowth Enabled in that Script?

You want to proactively create the tempdb files at their full sizes to avoid the files having to grow, because they might not all grow at the same rate. You could end up with a tempdb like this:

SQL Server will use the tempdb data files proportionally to the empty space inside them. That means that if some files are larger than others, they’ll likely get more activity using them. Then you can end up right back with the problem you started out with.

If you must have auto-growth turned on, the files should all grow in equal amounts — don’t set one of them to grow in 1% increments, and the others to grow by 100MB at a time.

Want to learn more? Take my class.

My Fundamentals of TempDB class is for curious folks who want to learn:

• What uses TempDB, and how that affects performance: not just temp tables and table variables, but also triggers, cursors, sorting indexes, workspace spills, the version store, and more
• How to host TempDB depending on how you use it: understanding what goes in the log file, the data files, why the number of data files matter, and whether your particular workload should be on SAN, local storage, or ephemeral storage, and why
• How to monitor it: after you understand what uses it and how to host it, you need to keep tabs on how its performance changes over time using DMVs, Perfmon counters, and the First Responder Kit scripts

Learn more and register now.