Increasing the HBA queue depth can increase storage performance, optimizing data across the connections to storage. In this best practice the HBA queue depth is increased to optimize performance. 

Overview

Relational database management servers, like SQL Server, servicing high throughput online transaction processing applications create bursts of disk I/O activity that benefit from careful design of the complete I/O path between server memory and permanent storage. When using Fibre Channel networking for access to shared storage, the SQL Server database server will include one or more host bus adapter (HBA) cards. In Fibre Channel networking, HBAs provide the same type of services to the host that a network interface controller (NIC) would for TCP/IP networks.

A key configuration parameter available on HBA cards is the HBA queue depth. The availability of an I/O buffer or queue on the HBA allows the host operating system to pass an I/O request on the HBA, even if the target storage device is unable to process the request immediately. This reduces the potential for blocking threads at the operating system level that would have to wait for the target to be ready for new I/Os. However, building up a significant number of queued I/Os on the HBA can create a large burst of I/Os to the storage target that could overwhelm the capability of the I/O ports and/or the storage controllers on the shared array.

When a large number of hosts connect to a single shared storage array, the storage administrators may request or require that server administrators decrease the HBA queue depth settings on connected servers, especially database servers. This helps alleviate resource contention concerns that can occur when hosts are saturating the storage front-end ports or controller processors with too many simultaneous requests. Our best practices development environment was dedicated to a single database server host, so this was not a concern for this project.

We focused on answering whether increasing the HBA queue depth would have a beneficial impact on workload performance. The default queue depth of most HBA cards is 32, pending I/Os. Check with your vendor to verify the setting for your equipment and use the management API or application to confirm that the setting has not been changed from the default before proceeding with testing or use in production.

Recommendation

Changing the HBA queue depth showed slight performance improvement in the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Server CPU utilization

These performance parameters saw no performance gains:

• PowerMax IOPS 
• PowerMax Average Read Response Time

PowerMax Average Write Response Time

The Emulex LightPulse HBA cards used in our testing lab had a default queue depth of 32. We tested a scenario with the queue depth increased to 64 and found slight performances increase in NOPM and TPM, and slightly increased server CPU utilization.   

Implementation Steps

To set lun_queue_depth, open the ESXi console and run this command:

esxcli system module parameters set -p lpfc_lun_queue_depth=64 -m lpfc

Run the following command to verify that the changes have been applied:

esxcli system module parameters  list  -m lpfc |grep lpfc_lun

References

VMware’s Set Storage I/On Control Threshold Value

Many database workloads are sensitive to storage performance. In this best practice, the virtual machine latency sensitivity has been changed to optimize performance. 

Overview

VMware vSphere provides the ability to adjust latency sensitivity to optimize low latency database workloads and similar applications. We used the default value for latency sensitivity (Normal) for our baseline test. The default latency sensitivity optimizes storage performance for throughput.  

This best practice recommends changing the latency sensitivity to High. Changing the latency sensitivity to High causes ESXi to optimize I/O operations for improved storage latency. This latency sensitivity change was completed for all the virtual databases. 

 Recommendation

Changing the latency sensitivity for the virtualized databases showed no improvement for these performance metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Server CPU utilization
• PowerMax IOPS 
• PowerMax Average Read Response Time
• PowerMax Average Write Response Time

Although there were no significant performance gains in our validation tests, this does not mean that other workloads would not benefit from setting the VM latency sensitivity to high. In the tests all four databases were set to a latency sensitivity of high, thus all databases were configured the same way, which is not how most systems are configured. Ideally, there will be a few critical systems with a latency sensitivity of high and others that are set to normal or low, which could yield a greater performance gain for the production systems. 

We recommend virtual machine latency sensitivity as a Day 3, Fine-Tuning activity. Consider changing the latency sensitivity to High for the SQL Server production virtual machines to optimize storage performance. 

Implementation Steps

Requirements:

ESXi 6.7 onwards requires full CPU reservation to power on a VM with hardware version 14 when latency sensitivity is set to High. For more information, see Adjust Latency Sensitivity.

1. Browse to the virtual machine in the vSphere Client and click the VMs tab. 
2. Right-click the virtual machine and click Edit Settings.
3. Click VM Options and click Advanced. 
4. Select the HIGH setting from the latency sensitivity drop-down menu. 
5. Click OK.

References 

VMware Docs: Adjust Latency Sensitivity

The threshold value of VMWare Storage I/O Control defines the upper limit of latency allowed for a datastore. In this best practice, the threshold for storage latency is lowered to optimize performance.  

Overview

Storage I/O Control allows the administrator to manually set a storage latency threshold for the datastores of the virtual machine. The storage latency threshold instructs the Storage I/O Control to monitor datastore(s) to ensure that performance is prioritized if the storage latencies exceed the threshold. In this best practice, the Storage I/O Control threshold is set to 5 milliseconds—the lowest possible value. 

Before changing the Storage I/O Control threshold, review VMware’s Set Storage I/On Control Threshold Value for more detail. VMware’s documentation notes that, “A very low value (lower than 20 milliseconds) will result in lower device latency and isolation among I/Os at the potential cost of decrease in aggregate datastore throughput.” As the HammerDB TPC-C workload is characterized by many small reads and writes, throughput was less of a concern. Customers should evaluate if setting the Storage I/O Control threshold would benefit their database workload. 

Recommendation

Changing the Storage I/O Control threshold to 5 milliseconds demonstrated no performance improvement in these metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Server CPU utilization
• PowerMax IOPS 
• PowerMax Average Read Response Time
• PowerMax Average Write Response Time

Although Storage I/O Control threshold showed no performance improvements in our validation tests, this best practice could benefit other workloads. Consider a mixed SQL Server ecosystem where a production database and a test database use the same infrastructure. Setting the Storage I/O control threshold to 5 milliseconds for production and higher for non-production systems like test databases could yield improved performance. This is a more realistic scenario than setting all four databases in our validation tests to 5 milliseconds. 

We recommend implementing this best practice as a post-installation step if further tuning of the database is required to improve storage latencies.

Implementation Steps

Verify that Storage I/O Control is enabled:

1. Click the datastore in the vSphere Client. 
2. Click the Configure Storage I/O Control tab and select Settings.
3. Click General.
4. Select the Enable Storage I/O Control check box. 
5. Select a value from the Manual drop-down menu. Note: In this best practice we selected 5 milliseconds. 
6. Click OK. 

References

VMware’s Set Storage I/On Control Threshold Value