5 Common Mistakes with Tempdb | Strate SQL

Good points, and they agree with what I've observed and posted on in the past.

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.