I've been asked to provide my input on best practices to improve tempdb performance. I realise that this is a case by case basis and tempdb in one estate may have different requirements to another due to workload volume and type however I was hoping to get an idea of best practices beyond my current suggestions.

At the moment we have 1 data file which I will increase to 4 to match the number of logical cores. My tempdb files currently reside on the same drives as other database files, they will be moved to their own to stop IO contention. I'm also to implement trace flags 1117,1118 to grow all files equally in a file group and solely use uniform extents. Finally, a further change I'll look to make is enabling Instant File Initialisation.

Beyond what I have above, are there any best practices I've missed? Also, it suggested that storing tempdb on it's own drive is a best practice but should all tempdb files be stored on the same drive or should .ldf,.mdf & .ndf be stored on their own volumes.


1 Answer 1

  • X files, with X being the number of processors up to a limit (8, 16 or so).
  • Preallocate size.
  • Always on separate volume because I really like the ability to check the IO load on that one. Tempdb overload happens rarely, but if it happens.... it is good to be able to set an alarm.

Regarding drives: if you CAN keep them on the same drive, you either have a very small database (in which case - get it on an M.2 / U.2 SSD with enough IOPS) or have a problem to start with - of the drive is "fake" (like a SAN delivering a LUN that in reality has a lot behind) I keep all my volumes these days on shared storage, and at the moment even on realtively slow HDD (7200 RPM), but it is backed by at the moment 6.4 TB of M.2 SSD which will even grow larger, so my read access time is pretty much guaranteed below one millisecond. Even with all ending up on the same discs, I really want separate volumes to check contention with one look.

Same with mdf and ldf - all on their own volume.

  • Thanks for your reply, I completely forgot to add sizing to prevent autogrowth! I just wanted to confirm, should each ndf be stored seperately? So ndf 1 stored on drive x ndf 2 on drive y etc? Thanks
    – Krishn
    Jul 10, 2018 at 15:04
  • Not necessarily. I personally care more about being able to nail "logs are slow" or "tempdb is overloaded" than every singel file. I may split them if they are LARGE - 50tb is not somethign i want to keep on one LUN/VHDX/whatever.
    – TomTom
    Jul 10, 2018 at 15:11
  • How about on user database ndfs. Should they just reside on a single drive for ndfs or would you suggest separating them? Again, I appreciate this will differ for each estate.
    – Krishn
    Jul 10, 2018 at 15:22
  • Likely. Generally I want to see easily when things overload, that is it. And I want SOME idea to pinpoint that.
    – TomTom
    Jul 10, 2018 at 15:29

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.