Optimize CPU best practices for SQL Server workloads

In this blog post, we will review the Amazon Elastic Compute Cloud (Amazon EC2) Optimize CPU feature and provide guidelines for utilizing this feature to reduce Microsoft SQL Server license cost on Amazon Web Services (AWS) without sacrificing SQL Server performance.

1. Introduction

Amazon EC2 offers a diverse range of instance types tailored to suit a multitude of use cases. Instance types comprise varying combinations of CPU, memory, storage, and networking specifications. Each instance type includes one or more instance sizes, resulting in over 750 different Amazon EC2 instances. This variety of instances provides AWS customers with the flexibility to tailor resources precisely to their application needs, while allowing customers to seamlessly scale resources for current and future demands of their specific workloads.

When considering SQL Server workloads, selecting the most suitable Amazon EC2 instance typically hinges on two key factors: the available memory (RAM) and the supported level of storage I/O capabilities. Generally, SQL Server OLTP (online transaction processing) workload performance is influenced by these underlying hardware characteristics.

For example, increased RAM would let SQL Server cache more data, thereby sending fewer read requests to storage. As you add more memory, the Page Life Expectancy counter should go up and the Physical Disk: Average Reads/sec counter should go down. A nice side effect is that the Physical Disk: Average Sec/Read counter (aka latency) should also go down, because the less work we make our storage do, the faster it’s able to respond.

On the other hand, prioritizing a faster storage subsystem with sufficient I/O operations per second (IOPS) ensures minimal data transfer latency and reduces queuing across all disks. It’s important to note that increasing CPU or memory resources cannot fully compensate for the performance impact of a slow I/O subsystem. By focusing on a robust storage infrastructure, organizations can maximize system efficiency and responsiveness, providing a solid foundation for optimal performance in SQL Server environments.

Table 1 lists attributes of the largest (“bare metal”) instances for Amazon EC2 instance types generally recommended for SQL Server deployments on AWS. Configuring smaller instances, within respective types, will provide fractional values for respective attributes. For example, the largest instance of r6idn type, r6idn.32xlarge (equivalent to r6idn.metal), will have the same number of vCPUs, amount of RAM, and IOPS capabilities, as listed in Table 1 for r6idn family. The smaller instances of the same type—for example, r6idn.16xlarge—will have half the number of vCPUs, amount of RAM, and supported IOPS as the larger r6idn.32xlarge instance.

Table 1. Selected Amazon EC2 instance families

2. What is Optimize CPU and what benefits does it bring?

Occasionally, due to factors like amount of memory or IOPS capabilities (see Table 1), you may need to select an Amazon EC2 instance with more vCPUs than necessary for your SQL Server workloads. Since SQL Server licensing is based on active CPUs visible to the operating system (OS), selecting an Amazon EC2 instance with more vCPU, may result in higher SQL Server licensing.

To help you optimize your licensing requirements and associated costs in these situations, AWS offers a feature called Optimize CPU available only for “bring your own license” (BYOL) deployments. Optimize CPU allows you to either disable hyperthreading or deactivate some processor cores for the Amazon EC2 instance, thus limiting the number of CPUs visible to the OS. This strategy allows you to benefit from other instance features, such as memory, networking, and IOPS, while reducing the required number of licenses.

Let’s assume that your SQL Server workload requires 200,000 IOPS, so to address this requirement, you select the r6idn.16xlarge instance, which is characterized by the set of parameters provided in Table 2.

Table 2. r6idn.16xlarge instance properties

With the default configuration, this instance makes 64 vCPUs visible to the OS, thus requiring 64 SQL Server licenses. Assuming that the SQL Server workload is not CPU-bound and CPU utilization is relatively low, you may use the Optimize CPU feature to reduce the number of licenses required.

Table 3 lists several potential Optimize CPU configurations for this case. The first line disables hyperthreading (threads=1) reducing the number of vCPUs and, correspondingly, the number of licenses required by 50%. The second line achieves a similar result by reducing the number of cores. If CPU utilization is still relatively low, you may both disable hyperthreading and reduce the number of cores for a 75% reduction in the number of SQL Server licenses.

Table 3. Optimize CPU samples

Optimize CPU feature allows you to disable hyperthreading and/or disable some cores – so, what is the optimal way to use Optimize CPU to reduce license cost without deteriorating SQL Server performance? To provide an answer to this question, it is necessary to run performance tests under various Optimize CPU configurations.

3. Performance Test Configuration

To run the performance tests, we will use the industry-standard HammerDB benchmarking tool using TPCC-like workload emulating OLTP databases. W will be using r6idn.32xlarge and r6idn.16xlarge instances configured with SQL Server 2022 Enterprise Edition and offering 400,000 and 200,000 max IOPS respectively. To test the performance on the r6idn.32xlarge instance with 1 TiB of memory, we generated an 8 TiB HammerDB database (75,000 warehouses); for a smaller r6idn.16xlarge instance with 512 GiB of RAM, we generated a 3.5 TiB database (30,000 warehouses). We configured HammerDB performance tests with the “Use All Warehouses” option to increase I/O load on the system.

To measure the performance of the database system, we ran a performance profile using HammerDB Autopilot with an increasing number of Virtual Users, up to the point where the transaction rate does not increase further. This stable value will be the accepted as the performance of the system. As SQL Server performance changes ever slowly with the increase of the number of virtual users, we configured Autopilot with the virtual users’ series with exponential progression. For each number of virtual users, we repeated the test 3 times and captured an average of 3 runs to achieve statistical consistence.

We configured the SQL Server deployment following the HammerDB Best Practices for SQL Server Performance and Scalability testing.

4. Performance Test Results

4.1. Test results for r6idn.32xlarge

Considering the size of the instance, we used the following series of Virtual Users: 181, 256, 362, 512, 724, and 1,024 in an exponential progression.

4.1.1. Tests with hyperthreading enabled

First, we evaluated SQL Server performance using the instance in its base configuration (128 vCPU), and then with a reduced number of cores (96 vCPU) without changing hyperthreading. Figure 1 presents results of these performance tests.

Figure 1. r6idn.32xlarge – Varying number of cores with hyperthreading

Figure 2 presents CPU utilization for this series of tests captured using Amazon CloudWatch. CPU utilization starts at a relatively low level of about 50%, but when load level reaches 512 virtual users (VUs) and above, utilization suddenly spikes to almost 100%.

Figure 2. r6idn.32xlarge – Varying number of cores with hyperthreading – CPU utilization

Despite spikes in CPU utilization, both systems achieved a provisioned level of 400,000 IOPS as shown in Figure 3.

Figure 3. r6idn.32xlarge – Varying number of cores with hyperthreading – IOPS utilization

4.1.2. Tests with hyperthreading disabled

Despites the spikes in CPU utilization, we decided to conduct a set of tests with varying number of cores with hyperthreading disabled. Results of these tests are presented in Figure 4.

Figure 4. r6idn.32xlarge – Varying number of cores without hyperthreading

As shown in Figure 5, as we reduced the number of active cores, CPU utilization increased, from 75% with 64 active cores to 88% with 48 active cores. As 88% CPU utilization is the practical maximum that a DBA would be comfortable with, we stopped testing at 48 active cores.

Figure 5. r6idn.32xlarge – Varying number of cores without Hyperthreading – CPU Utilization

For each of these test cases, IOPS utilization reached the provisioned IOPS level of 400,000 IOPS for loads with a high number of Virtual Users, as illustrated in Figure 6.

Figure 6. r6idn.32xlarge – Varying number of cores without hyperthreading – IOPS utilization

4.1.3. Summary of performance tests using r6idn.32xlarge

Performance test results for SQL Server deployed on a r6idn.32xlarge Amazon EC2 instance are summarized in Table 4.

Table 4. r6idn.32xlarge performance tests summary

4.2. Test results for r6idn.16xlarge

Disabling hyperthreading, as well as subsequently disabling some CPU cores, has limited to practically no effect on SQL Server performance, as shown by the performance test results in Table 4. But does this observation hold for Amazon EC2 instances of other sizes? To address this question, we ran a series of performance tests using SQL Server deployed on a r6idn.16xlarge instance.

Considering the smaller size of this instance, we used a smaller HammerDB database with the following series of Virtual Users (VUs): 64, 92, 128, 181, and 256. Performance test results for this configuration are presented in Figure 7 and summarized in Table 5.

Figure 7. r6idn.16hlarge performance test results with and without hyperthreading

Table 5. r6idn.16hlarge performance test results summary

Just as in the previous case, disabling hyperthreading and further reducing the number of active CPU cores does not affect SQL Server performance (within the precision level of HammerDB testing, which is about 1-2%) until the workload becomes CPU-bound.

5. Effect of Optimize CPU on price and price/performance

As demonstrated in the SQL Server performance tests, disabling hyperthreading, as well as disabling some CPU cores, does not bring meaningful reduction in performance if the SQL Server workloads are not CPU-bound. Now let’s see what effect applying the Optimize CPU feature has on the cost of SQL Server deployment.

To evaluate the cost reduction due to the application of the Optimize CPU feature, we had to account for the SQL Server license cost, which depends on purchase options and applicable discounts. However, for the following calculations, we used advertised Microsoft subscription license cost of $5,434/year for a 2-core pack, which results in $226 per core per month for SQL Server Enterprise Edition. With this assumption, Table 6 reflects the monthly cost for SQL Server deployed on r6idn.32xlarge.

Table 6. Optimize CPU benefits for SQL Server on r6idn.32xlarge

According to the findings in Table 6, CPU optimization can lead to a 56.25% decrease in the number of required SQL Server licenses. This translates to a more than 38% decrease in the monthly cost of deploying SQL Server, reducing the cost per 1,000 TPM from $23.69 to just $14.68, all without noticeably affecting SQL Server performance.

If a decrease in SQL Server performance of under 5% is acceptable, additional CPU optimization can reduce the number of required licenses by 62.5%. This translates to almost a 43% decrease in monthly cost, bringing the cost per 1,000 TPM from $23.69 down to just $14.21.

Table 7 reflects the performance and cost results for a SQL Server deployment on a r6idn.16xlarge Amazon EC2 instance. These results follow the same pattern, but in this case, we can reduce the number of active cores even further without a negative effect on SQL Server performance.

Table 7. Optimize CPU benefits for SQL Server on r6idn.16xlarge

According to the findings in Table 7, CPU optimization can lead to a 62.5% decrease in the number of required SQL Server licenses. This translates to an almost 43% decrease in the monthly cost of deploying SQL Server, reducing the cost per 1,000 TPM from $22.90 to just $13.22, all without noticeably affecting SQL Server performance.

If a decrease in SQL Server performance of under 4% is acceptable, additional CPU optimization can reduce the number of required licenses by 68.75%. This translates to an almost 47% decrease in monthly cost, bringing the cost per 1,000 TPM from $22.90 down to just $12.56.

6. Conclusion

SQL Server performance tests under various configurations, as discussed in this blog post, support the following conclusions and allow me to provide recommendations for effective use of the Optimize CPU feature:

• Disabling hyperthreading results in ~35% cost reduction (SQL Server EE) or 50% reduction in the number of required SQL Server licenses without a negative effect on performance.
• Further reduction of the number of active CPU cores further reduces cost up to ~47% with minimal (under 5%) to no reduction in SQL Server performance.
• Applying Optimize CPU feature results in significant reduction in cost per 1,000 TPM.
• When applying Optimize CPU, keep the IOPS per CPU in the range of 7,000 to 8,000; exceeding these limits may reduce SQL Server performance.

These results were obtained using synthetic OLTP-type workloads. Your results may differ depending on the type of your specific workload. Also, the cost and cost reduction estimates are calculated using baseline Microsoft license costs. If your license costs are different due to different licensing options or negotiated discounts, your cost reduction could be higher or lower than what is presented in this blog post.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.