Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Storage Best Practices > Day Three Best Practices > VMware Storage I/O Control: Threshold
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.
Category | PowerMax Storage |
Product | VMware Storage I/O Control |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine-tuning |
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, we set the Storage I/O Control threshold to 5 milliseconds (the lowest possible value).
Before changing the Storage I/O Control threshold, see VMware’s Set Storage I/On Control Threshold Value for more detail. That document states 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:
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 test database use the same infrastructure. Setting the Storage I/O control threshold to 5 milliseconds for production and to a higher value 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:
Additional Resources