From what I have read, best practices for configuring tempdb includes creating multiple data files. The rule of thumb as I understand it is if you should create the same number of data files as you have logical processors up to a max of 8. Then if you have tempdb contention add an additional file until either the contention goes away or you have added an additional file per 4 additional logical processors. So if you have 16 logical processors then you would go up to a max of 10 files.

What I'm curious about is if MAXDOP affects this at all? So if for example I have 12 logical processors. I have set my MAXDOP to 6 because they are broken into 2 NUMA nodes. Does this mean I should stick with 6 data files for tempdb? Or would I still have the 8 or 9 that is recommended for the total number of processors?


1 Answer 1


I wouldn't add single additional files, I would at least go up by 2 or 4 each time. Not many operations will work better at cores = 5 than cores = 4. I don't think you'll get much improvement when, faced with contention, you go from 4 files to 5. I would probably go up in groups of 4 (except when you have 6-core processors, for example, and want to test limiting SQL Server to exactly 6 cores), but by 16 if you still have contention I would have to wonder if this is really getting anywhere near solving the problem - especially because your best bang for the buck is going to be placing each of those files on its own set of spindles; highly unlikely you have this many completely independent disks.

As for the MAXDOP effect, I don't think you should think about MAXDOP that way. Unless you use affinity and prevent SQL Server from even seeing the other 6 cores, this doesn't mean that all queries will use the same 6 cores. So in theory, one query could use CPUs 1-6, and the other could use 7-12, and if contention could be relieved by letting each CPU access its own file (and it actually worked out perfectly), then certain concurrent ops could benefit from 12.

Another very easy way to solve tempdb contention problems is to throw a couple of SSD drives in the box (format one to 80%, fill it with a single pre-sized tempdb file, and use the other drive as standby - SSD lifespans can vary, but keeping a chunk unallocated can help extend that life quite a bit). This is even supported in a cluster as of SQL Server 2012. Takes a lot of thinking and effort out of trying to squeeze performance out of tempdb when slow storage is always going to be a bottleneck no matter how many files you try to configure.

Some background reading that might be useful, especially if you're stuck with old-fashioned spinny disks for tempdb:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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