What is the best controller disk configuration to run SQL Server on VMware 6.7?
Preamble
I had to build some SQL Server VMs on VMWare 6.7 hosts. I searched for good articles on VM configurations and I struggled to find enough and they all seemed to contradict each other. The company I work for are not too concerned with purchasing dedicated hardware for our SQL Servers as high performance is not an issue to the business so our SQL Servers run on the hardware which was available at the time. As a business, we are moving towards replacing running systems on bare metal to running on VMWare hosts which enables us to easily manage the hardware and build in resilience. I personally love the snapshot feature so I can alter the system knowing I can quickly restore the system to the snapshot.
Summary
I used the defaults to test my ESXi host and I only tested virtual controller and disk configurations. The answer is question is depends and you need to test but for me using the ParaVirtual controller(s) gave around 50% faster performance of the LSI Logic SAS (LSI) controller. Using a dedicated VMware ParaVirtual controller per drive gave a limited performance increase.
How a SQL Server uses storage
The way a SQL Server using its data files, tempdb and log files are all different. Data files – Theses need to read quickly as the server is waiting for them to be transferred to its internal memory. Data is written back as soon as the server can but at a lower priority as it relies on the log files to check for consistency if the server fails and has to recover. Log files – when the server makes a change to its data it writes to the log file first. Once the change has been written to the log file it is committed and the data is updated to the data file at a convenient time to the Server. TempDB files – the load on these files varies depending on how the system uses the tempdb files. One of the biggest impacts on the TempDB area is if the server is configured for optimistic locking as the version store is within the TempDB area.
My Configuration
Hardware
I have access to an old Dell PowerEdge R820 with Intel(R) Xeon(R) CPU E5-4650 0 @ 2.70GHz. It has one PERC H710P Adapter and the only drives I could find old spinning disks to I created a raid 5 with 900 GB 10 K drives (WD9002BKTG) and a raid 0 with two 300 GB 15 K drives (MK3001GRRB).
Test software (Crystal Disk Mark)
I used open source Crystal DiskMark 8.0.4 x64 to test the performance of the storage using the administrator account with its default settings of 1 GiB. I used DiskMark to test 9 times when I tested each configuration. Only the SEQ1M Q8T1 (Sequential: Block Size=1MiB, Queue=8, Thread=1) and RND4K Q1T1 (Random: Block Size=4KiB, Queue=1, Thread=1) are close to how a SQL Server would use storage to I will be ignoring the other two sets of results.
Virtual Machine
As we use Microsoft Software Assurance to allow us to legally move the VM around our VMWare farm the minimum licencing is 4 cores so set the VM to have 4 cores on one socket with 32 GB of RAM. I created the VM with four storage controllers, the maximum, with one LSI and three ParaVirtual.
Disk Configuration
I added three drives to the LSI controller running on the raid 5 sub system. on one of the ParaVirtual controllers I added three drives from the raid 5 sub system and three drives from the raid 0 sub system. I attached a disk to the two remaining ParaVirtual controllers with a disk from the raid 5 sub system.
Running the tests
The test VM was to the on VM running on the ESXi host.
Single controller to one or more disks
| Controller | Number of disks | SEQ1M Q8T1 read (average) | SEQ1M Q8T1 write (average) | RND4K Q32T1 read (average) | RND4K Q32T1 write (average) |
|---|
| LSI | 1 | 1,088.61 MB/s | 578.44 MB/s | 25.13 MB/s | 14.35 MB/ |
| ParaVirtual raid 5 | 1 | 1,596.52 MB/s | 819.17 MB/s | 75.98 MB/s | 16.92 MB/ |
| ParaVirtual raid 0 | 1 | 513 MB/s | 428 MB/s | 17.01 MB/s | 24.26 MB/ |
| LSI | 2 | 1,122.23 MB/s | 853.71MB/s | 33.47MB/s | 19.97 MB/ |
| ParaVirtual raid 5 | 2 | 1,212.72 MB/s | 827.30 MB/s | 23.59 MB/s | 9.06 MB/ |
| ParaVirtual raid 0 | 2 | 1067.91 MB/s | 525.54 MB/s | 55.89 MB/s | 41.33MB/ |
| LSI | 3 | 1,145.89 MB/s | 1,165.14 MB/s | 38.42 MB/s | 21.11 MB/ |
| ParaVirtual raid 5 | 3 | 1,356.21 MB/s | 1,288.38 MB/s | 28.82 MB/s | 22.20 MB/ |
| ParaVirtual raid 0 | 3 | 415.58 MB/s | 487.79 MB/s | 21.60 MB/s | 21.09 MB/ |
One Controller to one disk
| Controller(s) | Number of disks | SEQ1M Q8T1 read (average) | SEQ1M Q8T1 write (average) | RND4K Q32T1 read (average) | RND4K Q32T1 write (average) |
|---|
| LSI | 1 | 1,088.61 MB/s | 578.44 MB/s | 25.13 MB/s | 14.35 MB/ |
| ParaVirtual raid 5 | 1 | 1,596.52 MB/s | 819.17 MB/s | 75.98 MB/s | 16.92 MB/ |
| ParaVirtual raid 0 | 1 | 513 MB/s | 428 MB/s | 17.01 MB/s | 24.26 MB/ |
| LSI | 2 | 652.45 MB/s | 507.10 MB/s | 14.23 MB/s | 15.81 MB/ |
| ParaVirtual raid 5 | 2 | 1,698.28 MB/s | 1,151.22 MB/s | 41.61 MB/s | 20.87 MB/ |
| ParaVirtual raid 0 | 2 | 1,607.74 MB/s | 631.81 MB/s | 55.27 MB/s | 30.39 MB/ |
| LSI | 3 | 718.48 MB/s | 629.52 MB/s | 15.19 MB/s | 11.60MB/ |
| ParaVirtual raid 5 | 3 | 1,893.33 MB/s | 909.48 MB/s | 44.46 MB/s | 21.62 MB/ |
| ParaVirtual raid 0 | 3 | 859.07 MB/s | 765.63 MB/s | 16.93 MB/s | 28.01 MB/ |
Comparing the data
Using the data from using one ParaVirtual controller with one disk as the baseline to compare the the others then using three PavaVirtual controllers or two each with a disk with give roughly 5% increase in performance than a single PavaVirtual controller with one disk.
Concerns
The results from Crystal Disk Mark seemed to vary could be due to a number of factors, for example there is caching on the disk, controller and operating system which could contribute to variation in the results. This setup could have been typical five years ago but now most systems use Solid State Drives (SSDs) for storage which are very fast in both writing and reading compared to spinning disks.
Conclusion
The broad conclusion is using Crystal Mark Disk the VMware ParaVirtual controllers for your SQL Server storage will delivery better performance over the LSI Logic SAS upto around 50% faster with my configuration. The highest performance was with one to one relationship between the controller and the disk.
The best configuration depends on how complicated you wish to make it. It seems like the data and log files can exist on the same disk as they use the storage differently so with a disk connected a ParaVirtual controller for the data and log files and another ParaVirtual controller for a disk with the TempDB files would give good performance and a simple layout.
If you wanted a more complex solution then probably the VM would need to have four ParaVirtual controllers with the data, log and TempDB files spread over the disks connected to each controller. For example a user database could have four data files on four disks each attached to different controllers and using techniques like partitioning to equally share the data across the disks.
I was surprised at how badly the raid 0 striped 15 K spinning disk performed compared to the 10 K spinning raid 5, I assume due to the high performance of the raid controller the number of disks in the raid was the most important factor.
Running these tests gave an indication of possible configuration but I think have a process to load the SQL Server to test the configuration will be a better solution which I will be investigating.
