Home > Workload Solutions > SQL Server > White Papers > Dell PowerMax 2500 and 8500 Best Practices for Mission Critical SQL Server Databases > PowerMax Service Levels and SQL Server
With PowerMax high-capacity performance, often many databases and applications are consolidated into one storage system. To help with performance management based on business priorities, PowerMax manages the I/O latencies of the SGs based on their SL.
By default, the PowerMax storage system assigns an Optimized SL to new SGs. This SL receives the best performance that the system can provide but has the lowest priority in comparison with other service levels. Also, between SGs sharing the same Optimized SL, it is possible that increased activity of one SG affects the performance of another SG (such as a key mission-critical application) because they all share the same system priorities and performance goals. Using specific SLs can prevent this situation.
Use cases for SLs include “caging” the performance of a “noisy neighbor” (less important applications), prioritizing production compared to test/dev systems performance, and to satisfy the needs of service providers or organizations using “chargeback” in which their clients pay for a service level.
When using SLs, critical systems can be assigned a high SL such as Diamond, Platinum, or Gold to ensure that their performance goals have a higher priority over applications with a lower SL such as Silver, Bronze, or Optimized.
The following figure lists the SL prioritizations and their associated performance target goals.
When an SG with an SL of higher priority struggles to maintain its performance target goal, it can cause SGs with lower priority SLs to slow down (increase their I/O latency) to maintain its higher priority performance goals. For example, Diamond SGs can affect Platinum SGs, which can affect Gold SGs, and so on. Any SG with a higher priority (a non-Optimized SL) can affect optimized SGs.
The following figure shows the effect of SLs on a single SQL Server database workload. In this test, the SQL Server HammerDB database workload runs without interruption or change. Only the SL of the user database files (storage group: sqlLinuxInst2_userdb2_data) is changed every 30 minutes.
We see that a Bronze SL forced an average of 5 ms latency, which results in 6,000 IOPS. After the SL changed to Silver, the latency dropped to 2 ms and IOPS increased to 12,000. The Gold SL reduced latency to 0.3 ms and IOPS increased to 40,000. The Platinum and Diamond SLs did not provide much difference as they both performed at 0.2 ms latency and approximately 50,000 IOPS.
When an SL changes, the effect is immediate because it takes place at the PowerMaxOS software layer. We also see that SL latencies effect both reads and writes I/O response times.
The following figure shows the effect of SLs on two SQL Server databases. In this test, two HammerDB workloads ran without interruption or change. As seen on the left, the SL of the workload represented by the top line is set to the Diamond SL (simulating a key mission-critical application). The SL of the other workload, which is represented by the lower line, starts as the Bronze SL and is changes every 30 min until it reaches the Diamond SL.
We see that as the “caged” application improves its SL, it slowly takes more resources from the application with the Diamond SL, until both applications share the same SL and system resources. This result demonstrates the value of setting a lower priority SL to lower priority applications.