Optimizing performance and reducing licensing costs: Leveraging AWS Compute Optimizer for Amazon EC2 SQL Server instances

Amazon Web Services (AWS) recently added a new feature to AWS Compute Optimizer that detects Microsoft SQL Server workloads running on Amazon Elastic Compute Cloud (Amazon EC2) using machine learning. In this blog post, I will explain how this new feature helps you identify and optimize your SQL Server workloads.

Addressing challenges in rightsizing SQL Server instances on Amazon EC2

After migrating Microsoft SQL Server workloads to AWS, customers are looking for options to optimize their workloads running on EC2 for improved price-performance. However, due to factors such as limited expertise, time constraints, or difficulty in finding the right instance type, customers may not choose the most optimized instance type during migration. They may overprovision EC2 instances, leading to increased licensing, compute, and storage costs.

As AWS regularly introduces new instance types, it’s important for customers to evaluate whether these new instance types provide better price-performance. However, manual evaluations can be time-consuming and labor-intensive.

Leveraging AWS Compute Optimizer to overcome SQL Server rightsizing challenges

AWS Compute Optimizer is a service that helps customers avoid over-provisioning EC2 instances, EBS storage, and other resources while evaluating resources for price performance. To start using AWS Compute Optimizer, opt-in through the Compute Optimizer console and follow the getting started guide. After you opt-in, AWS Compute Optimizer analyzes your resource based on CloudWatch metrics, such as CPU, I/O, and network throughput of the instance, and I/O and throughput of attached EBS storage, from the past 14 days, with the option to extend it to three months for an additional charge. A minimum of 30 hours of runtime is required to generate recommendations.

For SQL Server, memory utilization is particularly important, however, since this is an OS metric, it isn’t available in CloudWatch by default. Customers can still receive compute recommendations without this metric. To enable memory metric, ingest it from sources like EC2 instances with the CloudWatch agent installed or from observability products like Datadog, Dynatrace, Instana, and New Relic.

Walkthrough

Working with AWS Compute Optimizer starts with the dashboard shown in Figure 1. This presents your resources in three categories: Under-provisioned, Optimized and Over-provisioned.

Figure 1 AWS Compute Optimizer Dashboard

If you select View recommendations on the dashboard, you will be taken to recommendations, as shown in Figure 2.  If AWS Compute Optimizer inferred the SQL Server workloads are running on Amazon EC2, including both license-included and bring-your-own-license instances, they will be tagged as such in the Inferred workload types column as shown in Figure 2. This feature allows customers to focus on SQL Server instances by filtering based on inferred workload type property and receive rightsizing recommendations to lower license and infrastructure costs.

Figure 2 Recommendations for EC2 instance with filtering

To view details, select the instance to evaluate. Compare the current instance type with recommended options, including price differences, vCPUs, Memory, Instance Store disk, and Network throughput, as shown in Figure 3. Graphed metrics can help you determine the optimal instance type for your application.

Figure 3 EC2 instance Recommendations comparison

EBS storage is essential to SQL Server workloads. The AWS Compute Optimizer analyzes EBS storage and provides recommendations for optimization. Review up to three recommendation options, each with different baseline IOPS, burst IOPS, or potentially different volume types, compared to your current EBS volume. Choose the most suitable recommendation and implement the changes via the Console or API.

Figure 4 EBS volume Recommendations

To view details, select the instance to optimize and click View Details. Compare the current EBS with recommended options, including Baseline and Burst IOPS, Baseline and Burst Throughput as well as Price Differences. Graphed metrics can help you determine the optimal instance type for your application.

Figure 5 EBS volume Recommendations comparison

AWS Compute Optimizer also provides a Performance Risk estimate, indicating the likelihood that the recommended resource may not meet the performance requirements of your workload. The service also checks if EBS storage IOPS has exceeded the limit of the attached EC2 instance.

Reducing SQL Server licensing and costs

SQL Server licensing is typically based on the number of vCPUs. Rightsizing your SQL Server instances can decrease the number of vCPUs and reduce instance costs and required licenses. AWS Compute Optimizer provides tailored rightsizing recommendations based on workload type, such as suggesting a smaller size of the same instance type or recommending a different instance type altogether. For example, it may suggest moving from an m5.4xlarge to an m5.2xlarge, reducing the vCPUs by half, or transitioning from an m5.4xlarge to an r5.2xlarge, which also has half the processors but the same amount of memory. Considering these recommendations, you can optimize your EC2 SQL Server instances for cost and performance.

Benefits of rightsizing SQL Server instances with AWS Compute Optimizer

• Cost savings: Optimizing SQL Server instances can result in cost savings on instances and reduce the number of required licenses.
• Improved performance: Choosing the optimized instance type for your SQL Server workloads can enhance performance based on workload requirements.
• Simplified management: The inferred workload detection feature for SQL Server simplifies instance management by providing targeted recommendations, reducing the need for manual intervention and guesswork.
• Scalability: As your workloads change or AWS introduces new instance types, AWS Compute Optimizer continually monitors and updates its recommendations, ensuring your SQL Server instances remain optimized for your current needs.

Conclusion

The addition of SQL Server inferred workload type detection to AWS Compute Optimizer can help organizations optimize resource usage and decrease costs, including licensing and infrastructure costs. By leveraging this feature, you can ensure that your SQL Server instances are tailored to your specific workload requirements, resulting in improved performance and cost efficiency.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your modernization journey today.