I've got a new server which will host MS SQL Server Virtual Machine along with Other VMs on HyperV. SQL Server will account for most(70%) of the Load.

The server has below Storage:
a. 2 x 240GB Read Optimised SAS SSD (Intel S4610) configured in RAID1 for Host OS
b. 2 x 480GB Mixed Use SAS SSD (KIOXIA PM5-V)configured in RAID1 - For DB Files
c. 4 x 1.2TB 12Gbps SAS 10KRPM HDDs configured in RAID5(no hotspare) - For DB Files + Other VMs

The SSDs are over-provisioned by leaving 10% space unallocated on both RAID volumes, yielding 200Gb and 400Gb usable space respectively.

Based on my research, below is the data I have:

  1. Data files (mdf) are generally read-intensive and can benefit from SSD.
  2. Log files (ldf) generate Sequential write IO. They don't benefit too much from SSD and can negatively affect SSD Endurance.
  3. TempDB is Read/Write intensive - It can cause SSD burnout. Can be slower on HDDs.

I was planning to put Data files on SSD and put Log Files & TempDB on SAS HDDs considering that tempdb can cause premature SSD failure. Does this still hold good in 2020? Am I being overcautious about this?

Any suggestions on how to configure storage?

Below are virtual file stats obtained from current(old) server by running query mentioned here: Virtual File Stats

  • The old server has D:(data) & E:(log) drives running on separate RAID5 and RAID1 Arrays respectively.
  • The server was rebooted at yesterday midnight and these stats represent usage at 3pm (15hrs of usage).
  • SQL Server Version: Microsoft SQL Server 2012 - 11.0.5343.0 (X64)


Your Answer

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

Browse other questions tagged or ask your own question.