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 )
....
Best Answer
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.