We are purchasing a Windows server with 12 NVMe PCIe cards for a highly read intensive database. Would there be any performance difference if we create one large RAID 10 set out of these drives and placing all database files on them? Is it better to create a few smaller RAID sets and separate tempdb, data, and log files? I know the answer is separate for traditional SATA based drives, just not familiar with the newer options.
Answer by anthony.green ·
I have not heard of any changes to how things should be done on newer tech, you just have to look at the way that Azure spin out SQL on VM's when using premium SSD drives, you get a Data drive and you get a Log drive. Now we don't know how those are presented at the back end, if its 1 large RAID pool or 2 smaller ones, but my gut would be it is 2 smaller ones to cater for the different IO patterns that data and log files have.
Data files are random IO so they will probably have 1 large RAID pool with disks which are specifically designed for random IOPS, where are logs are sequential IO, so again there will probably be 1 large pool which has SSD's designed for sequential IOPS.
So for me the advise still is the same, separate out what you can and place them on disks/ssd's designed for the right type of IO.