Storage configuration for faster queries

All we need is an easy explanation of the problem, so here it is.

Please advise on setting up storage allocation for a new SQL Server 2019 machine.

  • Ryzen 7 8Core with 64GB Ram
  • 4 local drives (2TBNVMe PCIe4 + 1TBNVMe PCIe3 + 2TBSSD + 4TBHDD)

I am going to allocate drives as below:

  • 2TBNVMe PCIe4 = MDF
  • 1TBNVMe PCIe3= LDF
  • 2TBSSD = OS + Temp Files
  • 4TB HDD = Daily SQL backups

I planned above setup to get optimum query results (we will have 10X100GB database files with concurrent expensive queries on daily basis and bulk inserts once in a week) – kindly advise if this the best local storage configuration.

Or should I assign NVMe PCIe3 to temp files for optimum querying and switch SSD to LDF (as we will have one bulk insert once in a week).

I am planning to increase RAM to 128GB once we have all DBs on.

I cannot have a fifth drive. LDF is not going to be heavily used. I am using simple recovery mode and DB shrink is on.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

kindly advice if this the best local storage configuration or shall I assign NVMe PCIe3 to Temp files for optimum querying and switch SSD to LDF (as we will have one bulk insert once in a week)

In this case – yes, it should be better to assign NVMe PCIe3 to Temp files for optimum querying and switch SSD to LDF.

Because from your description it seems that LDF is going to be used just once a week (and don’t forget you can also use BULK_LOGGED recovery model during bulk inserts to reduce amount of logging) with very low or none inserts/updates/deletes in between.

And tempdb would benefit from moving to a faster NVMe drive especially if your concurrent expensive queries going to use temp tables a lot.

On the other note amount of RAM seems small for 1 TB database, 128 GB RAM might be better for your case.

One more thing is try to separate OS from LDF/tempdb on a separate drive (fifth), because high load or unexpected growth of LDF or tempdb, can affect work of the OS and harm the system.

DB Shrink is NOT recommended, you’d better turn it off and pre-grow it to certain size that you know it won’t grow any more during bulk insert. Best is to have a database consultant or database vendor support team look into your planned setup, investigate on future usage patterns and make adjustments before you go live.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply