VM design and configuration

We used the following design principles and best practices to create the database VMs for the SQL Server databases.

SCSI controllers and virtual hard disks

We recommend using multiple SCSI controllers of type VMware Paravirtual to optimize and balance the I/O for the different SQL Server database hard disks, as described in this section.

The following table shows the recommended SCSI controller design for the OLTP and snapshot database VMs. The DATA and LOG disks are distributed across separate dedicated SCSI controllers because in a SQL Server OLTP workload both types of disks generate a high level of I/O to the storage.  On the other hand, the TEMP database disks generate relatively little I/O and can, therefore, exist together with the operating system volume on a separate dedicated SCSI controller.

Table 37.        SCSI controller properties in the OLTP and snapshot database VMs

The following table shows the recommended SCSI controller design for the DSS database VM. DSS workloads generate mostly read I/O to the DATA disks, with little I/O to the log disks. Also, tempdb usage increases significantly during DSS workload and can generate a significant amount of I/O. Because of this, as shown in the following table, the DATA disks and tempdb volumes are distributed across the three dedicated SCSI controllers for load balance, while the operating system, tempdb log, and DSS database log disks are located together on the first SCSI controller.

Table 38.        SCSI controller properties in the DSS VMs

All virtual hard disks for the VMs were created as VMFS6 datastores. Each datastore was then assigned to its respective VMs.

vCPU, vMem, and vNIC configuration

We left all the vCPU and vMem properties in all the SQL Server database VMs at their default values except for Memory Reservation. We allocated different quantities of vCPUs, vMems, and memory reservations to the different types of database VMs. For details about the values that we allocated during the testing of each of the use cases, see Table 2 and Table 7.

We added two virtual network adapters to each of the database VMs: one for in-band VM management and one for SQL Server public traffic. We configured the two adapters with the recommended type setting of VMXNet 3. For details about the configuration of virtual switches and physical adapters, see Compute and network design in Appendix B.

Enable disk UUID

For each VM, under VM Options advanced settings, we added the disk.enableUUID configuration parameter and set its value to TRUE. This setting ensures that the VMDK always presents a consistent disk UUID to the VM.

Guest operating system and SQL Server installation and configuration

To install and configure the Red Hat Enterprise Linux 7.6 guest operating systems, see the VMware document Installing and Configuring Linux Guest Operating Systems.

While configuring the Red Hat Enterprise Linux 7.6 guest operating system for SQL Server, we performed the following tasks:

• Used the tuned-adm command-line tool to set the latency-performance profile for an OLTP workload.
• Used the tuned-adm command-line tool to set the throughput-performance profile for a DSS workload.
• Followed Microsoft’s Performance best practices and configuration guidelines for SQL Server on Linux. Also, we added the Microsoft-recommended performance-related configuration parameters for the Red Hat Enterprise Linux operating system to the latency performance profile. Additionally, for our OLTP workload, we set vm.dirty_background_ratio to 20.
• Changed the disk label (DOS, by default) to GPT.
• Created disk partitions using the fstab or parted utility on storage devices. We chose the EXT4 file system while formatting the disks.
• Kept all the mounted file entries in /etc/fstab to enable automatic mounting when the server reboots.

To install and configure the SQL Server 2017 standalone database, see the following instructions from Microsoft: Quickstart: Install SQL Server and create a database on Red Hat.

After we installed SQL Server 2017 on Red Hat Enterprise Linux 7.6, we performed these configuration changes:

• Set Min server memory and Max server memory to the same value and left room for operating system overhead. For more information, see SQL Server Max Memory Best Practices.
• Changed the maximum degree of parallelism (MAXDOP) configuration option and the cost threshold for parallelism option after proper validation, because the query parallelism requirement changes according to the dataset and nature of the queries. For more information, see Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server and Configure the cost threshold for parallelism Server Configuration Option. During our study, we kept the MAXDOP value at its default value of 0 for the OLTP workload and at 8 for the DSS workload. Also, we kept the cost threshold for parallelism value at its default value of 5.
• Set the max worker thread value according to the workload and processor that were assigned to the SQL Server instance. For more information, see Configure the max worker threads Server Configuration Option. During our study, we kept the max worker thread at its default value of 0.
• Used multiple data files on different virtual disks and LUNs within the same filegroup.
• Allocated multiple tempdb data files to address tempdb contention issues. For more information, see Recommendations to reduce allocation contention in SQL Server tempdb database. For our study, we allocated eight files on a separate drive that was dedicated for tempdb with 8 GB per file.
• Segregated database data files, database log files, and tempdb files on separate drives that were mapped to dedicated virtual disks and volumes. For our study, we created two data files and one log file on dedicated drives.