0

We currently have tempdb (8 files) on solid state drive.

We know that primary mdf and transaction log ndf are recommended to be on different drives, to reduce contention. Should tempdb files be on different drives to have consistent principle, or can they all be on the same drive?

The following article stated they are all on same drive, however looking for a background technical explanation. Recommendations for more than one tempdb file

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
....
Improve this question
1
  • this is a different question, the link above does not comprehensive explain why tempdb can be one solid state drive
    – user129291
    Commented Jun 26, 2018 at 14:48

2 Answers 2

Reset to default
5

There are several factors that comes to play here:

How much I/O in general can a device (your SSD in this case) handle? Say that out of 100% in total for tempdb, the log (ldf) caters for 30%. Buying a new SSD and put the ldf on there will give you more IOPS and those 30% for the ldf won't be taken from the drive where the mdf/ndf is.

The "separate the log" made a lot of sense when we had single disks and there was a huge performance difference between sequential and random I/O. A transaction has to wait for the ldf records to be written to the disks (hardened). The ldf is (almost) only sequential I/O. Isolating ldf to a separate disk means the disk is in position for the next write operations meaning sequential I/O (no moving of the disk arm). How big difference is there between sequential and random I/O for an SSD disk? I don't know, but probably minuscule. So, this aspect is probably not relevant for modern disk subsystems.

So, IMO, it boils down to capacity. Can your SSD handle the I/O load with reasonable response time? If not, then you want more capacity - more SSD drives.

Improve this answer
1
  • All SSD is random I/O, mostly due to wear leveling software. This usually doesn't matter because it's worlds faster than spinning disks. And if your storage is a SAN or RAID then sequential vs random matters less and less because of the massive increase in effort to make sequential work well. Usually easier/cheaper to just buy more/faster disks.
    – Jonathan Fite
    Commented Jun 27, 2018 at 13:14
3

In my experience it is not necessary. If tempdb contention is causing severe performance issues then my preferred approach would be to optimise the queries that are hitting tempdb heavily, and ideally reduce the reliance they have on tempdb wherever possible.

While you probably would see some level of performance increase by splitting the files across multiple volumes, it is the same style of "fix" as throwing more memory at a system without having done basic query optimisation; hardware can hide a lot of fundamental issues with your code, but it doesn't eliminate them completely. Tackling the underlying issues will usually be a much better use of your time.

Improve this answer
0

Your Answer

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