Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files.

By having the larger number of files, you can increase the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O that SQL Server can push down to the disk level, the faster the database will run. With standard databases, SQL Server can cache a large amount of the data that it needs into memory. Because of the high-write nature of the tempdb, the data needs to be written to the disk before it can be cached back up into memory.

Though it sounds good in theory, is it really that good as a general optimisation? Is it something that may only apply for specific systems where IO is very high?

share|improve this question
up vote 11 down vote accepted

1/4 to 1/2 files to cores has long been the recommendation...

But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time. [link]

The last sentence has always been relevant. If you're not seeing contention, why add additional files? To play safe, most will add 2-4 files as a starting point for the majority of builds but beyond that, measure and react.

share|improve this answer
Thank you. That's great to know. – Preet Sangha Jan 23 '13 at 23:11

Like most general guidelines, it is a an oversimplification in its most positive light. At best, it is a good starting point (provided you don't aren't keeping the 1:1 core:data file ratio with a large amount of cores).

There is no replacement for proper design and proper follow-up monitoring and baselining. The reason behind having multiple data files for tempdb is to reduce and mitigate allocation page contention. There are numerous published posts on how to monitor this contention and take action accordingly. Below are a few resources:

Breaking Down TempDB Contention (Part 1)
Breaking Down TempDB Contention (Part 2)
Analyzing Tempdb Contention
Optimizing tempdb configuration with SQL Server 2012 Extended Events

But to answer your question, no this isn't a hard-and-fast configure-and-forget part of tempdb.

share|improve this answer
Thank for the links – Preet Sangha Jan 23 '13 at 23:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.