Home > Workload Solutions > SQL Server > Guides > Reference Architecture Guide—Ready Solutions for Microsoft SQL: Design for Dell EMC XtremIO > Combined performance of IOPS and latency
IT organizations and DBA teams typically deal with tradeoffs between IOPS and latency. For example, the greater the number of SQL Server databases the more IOPS on the storage array, resulting in higher latency times. This tradeoff between IOPS and latency happens over time. Initially, storage performance is good, and databases have low latency times. With time, more applications are added to the array and the tradeoff is weighted towards IOPS, thus impacting database and application performance.
In testing this architecture for Microsoft SQL Server, we wanted to aggressively consolidate databases to determine where the tradeoff between IOPS and latency was on the XtremIO X2 array. With 16 databases running in parallel, we surpassed the stated maximum of 220,000 IOPS for 72 flash drives by generating a total of 275,870 IOPS. The oversubscription of databases did not impact physical read and write latencies. Table 9 shows the average physical read and write latencies for the Windows and Linux VMs.
Table 9. Average latency for physical reads and writes by operating system
Type of reads/writes |
Windows |
Linux |
Physical reads for data (µs) |
382 |
377 |
Physical writes for data (µs) |
334 |
366 |
Physical writes for log (µs) |
269 |
229 |
Our findings show that there was no tradeoff between IOPS and storage latencies despite the oversubscription of databases. Customers can be confident that a properly sized SQL Server solution that is based on PowerEdge servers and XtremIO X2 arrays can scale while providing strong storage performance.