Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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:

share|improve this answer
add comment

Your Answer

 
discard

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.