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

by Rafet Ducic | on

Amazon Web Services (Amazon Web Services) recently added a new feature to Amazon Web Services 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 Amazon Web Services, 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 Amazon Web Services 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 Amazon Web Services Compute Optimizer to overcome SQL Server rightsizing challenges

Amazon Web Services 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 Amazon Web Services Compute Optimizer, opt-in through the Compute Optimizer console and follow the getting started guide . After you opt-in, Amazon Web Services 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 Amazon Web Services Compute Optimizer starts with the dashboard shown in Figure 1. This presents your resources in three categories: Under-provisioned, Optimized and Over-provisioned.

AWS Compute Optimizer Dashboard

Figure 1 Amazon Web Services Compute Optimizer Dashboard

If you select View recommendations on the dashboard, you will be taken to recommendations, as shown in Figure 2.  If Amazon Web Services 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.

 EC2 instance Recommendations comparison

Figure 3 EC2 instance Recommendations comparison

EBS storage is essential to SQL Server workloads. The Amazon Web Services 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.

EBS Recommendations

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.

Comparing EBS recommendations

Figure 5 EBS volume Recommendations comparison

Amazon Web Services 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. Amazon Web Services 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 Amazon Web Services 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 Amazon Web Services introduces new instance types, Amazon Web Services 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 Amazon Web Services 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.


Amazon Web Services can help you assess how your company can get the most out of cloud. Join the millions of Amazon Web Services 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 Amazon Web Services Contact us  to start your modernization journey today.