I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with.
My build has to run two versions of the same 35Gb database.
My intention is to split the 410Gb drive on the new box up as follows:
L:\ to hold log files. Based on a rule of 25% of the size of the database = 9Gb per DB = 18Gb).
T:\ to hold tempdb files. 4 data files (one per logical processor) at 1.5Gb each with a log file of 3Gb and a further 2Gb per DB for auto growth = 11Gb.
D:\ drive of 190Gb for data and an E:\ drive of 190Gb for all backups.
Does that sound like a sensible split?
Best Answer
In Working with tempdb in SQL Server 2005 Microsoft writes:
Now although this was initially written for SQL Server 2005, it is still referenced in the article:
...where Microsoft writes:
So the first article references the old article, but with a twist, because further down Microsoft writes:
Generally placing (user) database files on other drives can be considered a good idea, but doesn't have to be. These factors depend on your storage sub-system and the throughput (I/O) the sub-systems provide.
There is an Q&A on Disk Configuration on Server Fault:
Where Paul Randal's answer starts off with:
Seeing as Paul was in the SQL Server Development Team I would tend to give him some advance credit for his answers, especially since he know's a lot about the internal working of SQL Server. He has revised some of the information as time passed, but his blog posts are good starting points.
An equally interesting answer in the same Q & A references the Microsoft article Optimizing tempdb Performance equally states:
There is the equally interesting piece of information tightly hidden in the new Microsoft Docs for the newer versions of SQL Server, to be found in:
As you can see, it is not only about placing the correct files on the adequate storage, but also about storing the relevant data in separate filegroups. (Which is a topic for high end configurations)
Even Azure has differences in the disk setup page:
In the example screen shots provided in the example, the disk can be stored in different lcoations and on different storage (SSD, Premimum SSD, ...)
Answering Your Question
Depending on your Storage setup, you might be able to get some fast SSD storage for your SQL Server instance (VM). Put the Transaction Log Files and maybe even the TEMPDB files on those disks. Create separate drives.
Put the MDF and NDF files of the user databases on the slower disks / storage.
Ensure you have enough RAM to support data buffering as this will reduce the I/O required on the MDF and NDF files (disk storage).
In the end it depends on a lot of factors. Go and read some of Paul Randal's and Brent Ozar's articles regarding RAM, I/O, tempdb and disk partitioning (Another aspect; you are formattting your data disks with 64kb cluster size?)
It really does depend on what you have and where you want to end up.