5 Common Mistakes with Tempdb

One of the things I get to do pretty often is to visit with clients and discuss how they’ve implemented their SQL Server environment.  And if the title of this post is any indication of the contents there are a number of issues that I tend to be able to make recommendations on in all of these environments.

Below are five of the most common tempdb items that I come across:

1. Just a single tempdb data file: As a rule of thumb, Tempdb should have as many data files as there are CPUs/cores for the SQL Server instance.  This prevents helps contention that can occur during peak tempdb activity.  Of course, too many files can be a problem as well so if you are thinking of adding more than 8 data files, look at doing some testing to verify the additional files are not causing performance issues of their own.
2. Multiple tempdb transaction logs: The transaction log, of course, serves a much different purpose than the data files.  It’s there to log all of the transactions from tempdb and performs this function sequentially.  No matter how many files you have only one will be active at any time.  Having extra files won’t necessarily change performance or how tempdb behaves but it’s not going to provide any benefit.
3. Size Doesn’t Matter: The data files for tempdb should all be the same size and large enough to handle the workload for all 24 hours of the day.  If one of the files grows for some reason, the other files should be grown as well to keep all of the files the same size.  This helps SQL Server distribute the work more better evenly across the tempdb data files.  Also, there are a number of operations that utilize tempdb – make sure you have the room.  Skipping DBCC CHECKS because tempdb is too small is a recipe for disaster a la mode.
4. Tempdb and user database data files sharing a disk: Tempdb should be separated and isolated from user databases.  This creates contention between the user databases and tempdb.  Contention with temporary objects when executing a query and reading data from a user data typically has one unfortunate result… poor performance.  And no one likes that…
5. Tempdb on the slowest disk: Ugh!  Just because the databases on the server aren’t creating temporary tables doesn’t mean tempdb isn’t being used.  Queries that use operations such as Sort, Hash Match, and Spool will use tempdb and the faster the disk for tempdb the faster the performance of the query.  Tempdb has a lot of activity from all of the databases on the server and if it can’t push it’s IO quickly nearly everything will suffer.

I’ve kept these items fairly short and without a lot of detail because if you are doing any of these you need to read up on tempdb performance in Books Online and read the whitepaper Working with Tempdb.  Tempdb is a crucial resource in the SQL Server environment and it’s configuration should be a first thought rather than an after thought to prevent from having it bite you in the face.

Taking care of these kinds of easy configuration issues will help make certain that your tempdb is Harder, Better, Faster, Stronger.