4

According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk.

I have a virtual server which had originally 4 LUNs from the same RAID 10 pool. By the help of volume manager, I converted those 4 LUNs into 4 different volumes.

Now the question is, will it make any difference by moving tempdb into separate volume, or it is just fine to keep them with other SQL Server files in terms of performance?

Laurenz Albe
  • 30,193
  • 3
  • 26
  • 50
RaufDBA
  • 1,121
  • 6
  • 19

2 Answers2

4

There are several potential benefits.

  1. Constraining TempDb Growth
  2. Making disk-level performance counters more informative.
  3. Separating the IO Queues so a log write IO never waits behind TempDB IOs in Windows.
  4. Simplifying storage reconfiguration both in Windows and on the SAN.
  5. Scaling across SAN controllers and enabling per-lun SAN optimizations.
David Browne - Microsoft
  • 39,093
  • 3
  • 40
  • 88
  • Thank you fro pointing out these benefits. Could you please clarify the 3rd benefit? – RaufDBA Jun 09 '20 at 21:44
  • 2
    There can be per-disk IO queueing in Windows. EG if there are 32 outstanding IOs sent to the driver, Windows may wait to send any more until some of the outstanding ones complete. So it's possible that a log write gets stuck behind a bunch of TempDb IOs if they are on the same disk. No guarantee that sending all the IOs would be any better, but there's a good chance the log write could be write-cached at the SAN controller and wouldn't actually wait for disk. – David Browne - Microsoft Jun 10 '20 at 13:39
  • Got it. I really appreciate your valuable time. – RaufDBA Jun 10 '20 at 17:34
  • Sir, I would like to ask one more question related to per-disk IO queueing. Does this theory fit to virtual server? I mean if I have 2 volumes on the virtual server, do volumes has per-disk IO queueing as well? – RaufDBA Jun 15 '20 at 18:06
  • 1
    In theory yes. But it's probably rare that it's a serious issue. – David Browne - Microsoft Jun 15 '20 at 18:15
0

Tempdb can grow immense if, for some reason, a nasty query. Is executed that eats up tempdb. Putting tempdb on a separate drive, which is the same storage under the cover, makes sure that your data or log volume won’t get full. If the tempdb volume gets full, you will receive an error message that tempdb is full, instead of your db possibly stopping because the data or log volume is full.

Niels Broertjes
  • 445
  • 1
  • 11
  • You can set the max size for the files in tempdb and all databases. If you don't want it to grow over say 100GB it won't if you set the files to max out before that. What ever is running when the tempdb fills, will probably die. But if even if your tempdb fills a shared drive with your data, it will only stop you from adding new data, and optimally you have already allocated sufficient space for your data, so with good management, none of what you suggest need occur. – James Jenkins Jun 10 '20 at 18:24
  • P.S. As David points out, it is not a good idea. But your reasons are not the best ones. – James Jenkins Jun 10 '20 at 18:26
  • @JamesJenkins You are indeed correct. I actually always looked at it from an angle of performance (if on different physical disks) and growth control. I did not think of limiting it, because I always put it on a different drive, which would in practice be the same. The reasons David is mentioning are definitely more helpfull and I learned again from that as well, so I up-voted his answer earlier on as well. Iam not sure If I understand you correctly in your last comment though. What are you referring to as not being a good idea? I know my reasons are not the best ones. :-) – Niels Broertjes Jun 11 '20 at 05:31
  • In some scenarios (like low budget physical machines) giving the tempdb it's own drive is not practical, and if the performance trade off is acceptable, All of the risks you mention can be mitigated. Yes a nasty query can do bad thing to tempdb, but if you plan for it; other things on the same disk need not be at risk for loss of space. – James Jenkins Jun 11 '20 at 12:31
  • 1
    I posted a new question here https://topanswers.xyz/databases?q=1106 and was going to write an answer but real work calls for me. – James Jenkins Jun 11 '20 at 12:40
  • @JamesJenkins Haha, hope there weren't any issues with tempdb. Btw, didn't know the site existed, so will check that out as well. :-) – Niels Broertjes Jun 11 '20 at 16:20