MS SQL Server 2016 Oprimal Disk Configuration [closed]

Posted on

Question :

New server build for SQL SERVER STANDARD 2016 install. Total of 12 disks: 6 standard 15K HDs and 6 SSDs. This is the disk setup I am going for.
Could this setup be improved on, if so how what would change?


I could move TEMP DB mdf to DATA Drive and have TEMP DB log with other DB Logs on SSD LOG drive.

  • This will be for a SQL Server Production environment only.

  • Optimizing for speed but also HA to an extent.

  • Backups will get copied to an offsite location throughout the day.

  • Server will be mirrored not AG. Although mirroring is deprecated, due to install being Standard Edition I don’t see benefits of AG at this point with 2 servers.

  • Biggest waits are LATCH_EX followed by CX_PACKET

Also looking to replicate some data for reporting purposes.

LOG average file size is 9.16 MB over past 6 months, Log backup every 15 mins
MAX size has been 389.47MB in past 6 months

TEMP DB info for past 6 months

enter image description here

Answer :

Looks fine to me.

Because of the way that SQL Server schedules writes, keeping logs and data on separate volumes doesn’t save as save as much performance as many think for many workloads (it does have a large effect on some workloads though, so YMMV), and I expect this will be less still on SSDs (as you don’t have the latency of head movements flipping between the log and data files to worry about) so if you expect a lot of tempdb activity you might be better off keeping the logs with the data files and leaving tempdb on its own to keep the IO bandwidth of that array to itself.

Of course this is very dependant on actual application workloads so my gut thought above could be completely wrong for your DB(s), so if possible benchmark before committing to anything in production.

Also Cody’s comment regarding spare drives is critical: make sure there are some on-site or very nearby. From a HA standpoint mirroring will protect you to an extent should a whole array fail (without mirroring take “or nearby” from the above and make sure there are definitely spares on-site and ready to go) but I’d still feel safer knowing an array can start rebuilding ASAP after a single drive failure to reduce the chance of a dual drive failure. Also check with your infrastructure provider to make sure the machine supports hot-swap (or if not set your recovery plans accordingly to take into account entirely stopping one mirror temporarily while a replacement is installed).

Leave a Reply

Your email address will not be published. Required fields are marked *