0

Currently we have 4 cores and 4 tempdb files. I just came on as the DBA about 2 months ago and, for some reason, 3 of the tempDB files are 2GB and 1 is 1GB. Obviously, that is not best practice.

Being the OCD and proactive person that I am, I want to resize that file to match up with the other 3.

But wait, there's more. It's used on a system that has an application that gains performance by NOT using any parallelism, so we have it disabled. MAXDOP = 1, cost threshold = 0. It's the only application on the system and the SQL isntance is basically designed for this one application.

What are everyone's thoughts on this? Will it help with any slowdowns that were seeing, given that our MAXDOP = 1? Also, what implications/issues should I be on the lookout for when I change the file size? I'll be doing it at night, off production hours.

Thanks for the help guys.

Improve this question

1 Answer 1

Reset to default
4

Individual queries don't use parallelism? So what? If you have two completely different users running different queries at the same time, they too can make use of different tempdb files.

Of course it doesn't help all that much if you have four tempdb files all on the same volume - the real gain is when you can spread it out into multiple I/O paths.

Hard to speculate on "any slowdowns you're seeing" without more details.

Shouldn't be any issues with changing the file size. Just make sure they are all exactly the same size, have the exact same autogrow settings, and check to see if you should run trace flags 1117 and 1118.

Improve this answer
6
  • Great info as usual Aaron. They are all on the same volume but it's a VM utilizing a SAN so we are limited with options there. Just trying to get everything synced with best practices. We have another one with 12 processors and 10 tempdb files. That's next on the list to get up to date. Any advice on adding the 2 additional files or gotchas there?
    – Kris Gruttemeyer
    Aug 25, 2014 at 16:21
  • 2
    @Kris on that server I would actually start at 4 or 8 before assuming you should always have tempdb files = number of cores. (I would never do 10. Should be a "normal" power of 2 - 2,4,8,16,32 IMHO.) But really, no, there is no "advice" around adding files. You just add the files (hopefully you have instant file initialization enabled, so it should be, well, instant). SQL Server will just start using that file when it needs to.
    – Aaron Bertrand
    Aug 25, 2014 at 16:28
  • Yeah, when I started here it was already 10 so I can't really do too much there and I don't think we'd want to back it down to 8. What about jumping to 16? Wouldn't that potentially cause issues with that many files and only 12 processors?
    – Kris Gruttemeyer
    Aug 25, 2014 at 16:34
  • I don't think you will gain anything with 16 files. I also don't know what you think you will lose reducing the current number - do you have any evidence 10 files are helping you more than 4 or 8 would? Do you even have any tempdb contention? How do you know tempdb configuration is even a "problem" you should be spending time on?
    – Aaron Bertrand
    Aug 25, 2014 at 16:44
  • 1
    So many recommend that just like so many recommend that PLE should be above 300 and if it is you're doing just fine. Those recommendations get outdated, no matter how many times people have copied them.
    – Aaron Bertrand
    Aug 25, 2014 at 16:51

Your Answer

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

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