How do I determine the right Amazon EC2 instance type for my SQL Server workload?

Resolution

Use Compute Optimizer

Use AWS Compute Optimizer to receive automated guidance about instances based on your workload. Compute Optimizer can help you define the correct size of instance for your SQL Server workload. AWS Compute Optimizer can also help you reduce your licensing costs.

For more information, see Optimizing performance and reducing licensing costs: Leveraging AWS Compute Optimizer for Amazon EC2 SQL Server instances.

Check your CloudWatch metrics

Check the Amazon CloudWatch metrics for your instances to understand your performance and workload requirements. You can also use CloudWatch agent to collect the following SQL Server metrics:

• Use Batch Requests/sec to measure the number of SQL batch requests per second.
• Use Page Life Expectancy to understand how long pages stay in the buffer pool
  Note: Higher values show better memory performance.
• Use Buffer Manager to track memory usage and buffer cache efficiency.
• Use Processes Blocked to identify blocking and locking issues
• Use SQL Recompilations/sec to measure query plan recompilations.

For more information, see Monitor your Microsoft SQL Server using custom metrics with Amazon CloudWatch and AWS Systems Manager.

Evaluate performance criteria

For SQL Server workloads, configure the following settings.

Memory (RAM)

Large memory allocations improve SQL Server performance. Higher Page Life Expectancy values show that data pages remain in memory longer. This configuration reduces the need for disk reads. As a result, it's a best practice to use memory-optimized instance families for SQL Server workloads. Use R family for standard SQL Server workloads and X family instances for memory-intensive SQL Server databases. You can also use M family instances that balance high memory and compute capacity for large SQL Server deployments

Storage performance (IOPS)

Storage subsystem performance affects SQL Server performance. Evaluate the maximum input/output operations per second (IOPS) that your workload requires. Make sure that you select an instance type that supports the required IOPS for your database operations.

Processor compatibility

SQL Server requires Intel or AMD processors. Because SQL Server doesn't support ARM-based processors, you can't use AWS Graviton instances for SQL Server workloads.

For more information, see Optimize CPU best practices for SQL Server workloads.

Understand the SQL Server performance benchmarks

To understand performance across instance types and storage configurations, review the SQL Server Performance Benchmarking on AWS whitepaper. The whitepaper also provides best practices for SQL Server in Amazon EC2 and performance benchmarks for common SQL Server workloads.

Specify CPU options for your instance

After you select your instance type, specify CPU options for your instance to reduce licensing costs but keep high performance. Deactivate hyper-threading to reduce your core count. Also, configure the number of vCPUs to only use the minimum number of cores.

For more information, see Optimize CPUs best practices for SQL Server workloads - continued.