SQL Transaction Processing and Analytic Performance Price-Performance Testingv1.0

1. Executive Summary

The fundamental underpinning of any organization is its transactions. They must be done well, with integrity and performance. Not only has transaction volume soared recently, but the level of granularity in the details has reached new heights. Fast transactions significantly improve the efficiency of a high-volume business. Performance is vital.

There are a variety of databases available for transactional applications. Ideally, any database would have the required capabilities; however, depending on the application’s scale and the chosen cloud, some database solutions can be prone to delays. Recent information management trends see organizations shifting their focus to cloud-based solutions. In the past, the only clear choice for most organizations was on-premises data using on-premises hardware. However, the costs of scale have chipped away at the notion that this is the best approach for some, if not all, companies’ transactional needs. The factors driving operational and analytical data projects to the cloud are many. Still, advantages like data protection, high availability, and scale are realized with infrastructure as a service (IaaS) deployment. In many cases, a hybrid approach is an interim step for organizations migrating to a modern, capable cloud architecture.

This report outlines the results from two GigaOm Field Tests (one transactional and the other analytic) derived from the industry-standard TPC Benchmark™ E (TPC-E) and TPC Benchmark™ H (TPC-H). The tests compare two IaaS cloud database offerings running Red Hat Enterprise Linux (RHEL), configured as follows:

1. RHEL 8.6 with Microsoft SQL Server 2022 Enterprise on r6idn.8xlarge Amazon Web Services (AWS) Elastic Cloud Compute (EC2) instances with gp3 volumes.
2. RHEL 8.6 with Microsoft SQL Server 2022 Enterprise on a E32bdsv5 Azure Virtual Machine (VM) with Premium SSD v2 disks.

Both are installations of Microsoft SQL Server 2022. We tested RHEL 8.6 with a preconfigured machine image. The report is based on Linux. The Windows based performance study was conducted earlier this year.

Testing hardware and software across cloud vendors is challenging. Configurations can favor one cloud vendor over another in feature availability, virtual machine processor generations, memory amounts, storage configurations for optimal input/output, network latencies, software and operating system versions, and the benchmarking workload. Our testing demonstrates a slice of potential configurations and workloads.

As the report sponsor, Microsoft selected the particular Azure configuration it wanted to test. GigaOm selected the closest AWS instance configurations for CPU, memory, and disk configuration.

We leave the issue of fairness for the reader to determine. We strongly encourage you to look past marketing messages and discern what is of value. We hope this report is informative and helpful in uncovering some of the challenges and nuances of platform selection. Price-performance intends to be a normalizer of performance results across different configurations.

The parameters to replicate this test are provided. We used the BenchCraft tool, audited by a TPC-approved auditor who reviewed all updates to BenchCraft. All the information required to reproduce the TPC-E results are documented in the TPC-E specification. BenchCraft implements the requirements documented in Clauses 3, 4, 5, and 6 of the benchmark specification. There is nothing in BenchCraft that alters the performance of TPC-E or this TPC-E-derived workload.

The scale factor in TPC-E is defined as the number of required customer rows per single tpsE. We changed the number of initial trading days (ITD). The default value is 300, which is the number of eight-hour business days to populate the initial database. Instead of an ITD of 300 days, we employed an ITD of 30 days for these tests, resulting in a smaller initial database population in the bigger tables. The overall workload behaves identically with ITD of 300 or 30 as far as the transaction profiles are concerned. Since the ITD was reduced to 30, any results would not comply with the TPC-E specification and, therefore, not be comparable to published results. This is the basis for the standard disclaimer that this is a workload derived from TPC-E.

However, BenchCraft is just one way to run TPC-E. All the information necessary to recreate the benchmark is available at TPC.org (this test used the latest version, 1.14.0). Just change the ITD, as mentioned above.

Azure E32bds_v5 outperforms AWS r6idn in performance and cost-effectiveness for mission-critical workloads. In the TPC-E benchmark test, Azure had 1,435 tpsE compared to 1,179 tpsE for AWS, with 22% better price-per-performance and a more cost-effective three-year commitment. Additionally, Azure provides a 21.7% performance improvement over AWS in SQL Server 2022 Enterprise on RHEL.

The TPC-H results for SQL Server 2022 Enterprise on RHEL show that Azure E32bds_v5 has a higher best queries per hour (QPH) than AWS r6idn, with 367 QPH compared to 305 QPH. This suggests that Azure E32bds_v5 is more performant for these types of queries than AWS r6idn. The difference in performance is quite significant, with Azure E32bds_v5 providing a 20% performance improvement over AWS r6idn.

The results also show that AWS r6idn produced a 28% higher price per performance than Azure E32bds_v5 for the same TPC-H workload on SQL Server 2022 Enterprise running on RHEL with license mobility. This suggests Azure provides better value for money than AWS for this particular setup. Additionally, both solutions offer significant cost savings over traditional on-premises infrastructure.

We have provided enough information in the report for anyone to reproduce these tests. You are encouraged to compile your own representative queries, data sets, data sizes, and test compatible configurations applicable to your requirements.

2. Cloud IaaS SQL Server Offerings

Relational databases are a cornerstone of an organization’s data ecosystem. While alternative SQL platforms are growing with the data deluge and have their place, workload platforming decision-makers usually choose the relational database. This is for a good reason. Since 1989, Microsoft SQL Server has proliferated to near-ubiquity as the relational server of choice for the original database use case—online transaction processing (OLTP)—and beyond. Now, SQL Server is available on fully functional infrastructure offered as a service, taking complete advantage of the cloud. These infrastructure-as-a-service (IaaS) cloud offerings provide predictable costs, savings, fast response times, and strong non-functionals.

Red Hat Enterprise Linux on Microsoft SQL Server 2022 Enterprise on Azure Virtual Machines Storage Options

Azure recommends Premium Managed Disk or Ultra Disk for operationally intensive, business-critical workloads. We chose to test the latest Premium SSD v2 Managed Disks. Premium SSD v2 Managed Disks are high-performance SSDs designed to support I/O intensive workloads and provide high throughput and low latency but at a lower cost than Ultra Disk. Premium SSD v2 Managed Disks are provisioned as a persistent disk with configurable size and performance characteristics. They can also be detached and reattached to different virtual machines.

The cost of Premium SSD v2 Managed Disks depends on the capacity of the disk, input/output per second (IOPS), and desired throughput (in MB/second). Several persistent disks attached to a VM can support petabytes of storage per VM. Premium SSD v2 disks can achieve up to 64 TB in capacity, 80,000 IOPS, and 1,200 MB/s per disk. This translates to less than one millisecond latency for read operations; thus, the read/write caching capabilities of Premium SSD disks are no longer needed. Premium SSD v2 Managed Disks are supported by several VM types.

Red Hat Enterprise Linux on Microsoft SQL Server 2022 Enterprise on Amazon Web Services Elastic Cloud Compute (AWS EC2) Instances Storage Options

Amazon Web Services offers Elastic Block Store (EBS) as an easy-to-use, high-performance block storage service designed for use with Amazon Elastic Compute Cloud (EC2). EBS supports a range of workloads, like relational and non-relational databases, enterprise applications, containerized applications, big data analytics engines, and file systems. With EBS, AWS customers can choose from four volume types to balance optimal price and performance. You can achieve single-digit, millisecond latency for high-performance database workloads.

Amazon EBS has three types of solid-state drives: General Purpose SSDs (gp2 and gp3) and Provisioned IOPS SSD (io1). Provisioned IOPS disks are more akin to Azure Ultra Disk, so we chose General Purpose SSD (gp3) volumes to balance price and performance for these workloads. AWS recommends this drive type for most workloads. Gp2 tends to underperform unless relegated to system boot volumes, virtual desktops, low-latency applications, and development/test environments.

For the test, we chose AWS’s Nitro-based instance r6idn. Like Azure, we used a solid-state drive for the SQL Server temporary database (tempdb) on r6idn.

One of our main objectives in this benchmark is to test an I/O intensive workload on Amazon and Azure’s speed-cost balanced SSD volume types head to head. We want to understand both the performance and price-per-performance differences between two leading cloud vendors’ SQL Server offerings.

3. Field Test Setup

GigaOm Transactional Field Test

The GigaOm Transactional Field Test is a workload derived from the well-recognized industry-standard TPC Benchmark™ E (TPC-E). Aspects of the workload, such as transaction mix, were modified from the standard TPC-E benchmark for ease of benchmarking. As such, the results generated are not comparable to official TPC Results. From tpc.org:

TPC Benchmark™ E (TPC-E) is an OLTP workload. It is a mixture of read-only and update-intensive transactions that simulate the activities found in complex OLTP application environments. The database schema, data population, transactions, and implementation rules have been designed to broadly represent modern OLTP systems. The benchmark exercises a breadth of system components associated with such environments.

The TPC-E benchmark simulates the transactional workload of a brokerage firm with a central database that executes transactions related to the firm’s customer accounts. The data model consists of 33 tables, 27 of which have foreign key constraints. The TPC-E results are valuable to all operational functions of an organization, many driven by SQL Server and frequently the source for operational interactive business intelligence (BI).

Field Test Data
The data sets used in the benchmark were generated based on the information provided in the TPC-E specification. The scaling determined the initial data volume of the database. For example, a total of 800,000 customers is multiplied by 17,280 to determine the number of rows in the TRADE table: 13,824,000,000. All of the other tables were scaled according to the TPC-E specification and rules. Besides the scale factor, the test offers a few other “knobs” we turned to determine the database engine’s maximum throughput capability for AWS and Azure.

We completed three runs per test on each platform, with each lasting at least two hours. We then took the average transactions per second for the last 30 minutes of the test runs. A full backup was also restored to reset the database to its original state between each run. The results are shared in the Field Test Results section.

Database Environments
Selecting and sizing the compute and storage for comparison is challenging, particularly across two different cloud vendor offerings. There are various offerings between AWS and Azure for transaction-heavy workloads, and at the time of this testing and publication, there was no exact match in processors or memory.

For AWS, we tested a new generation that has locally attached SSDs, the r6idn, so that we could put the tempdb on those drives and see the performance difference.

On the Azure side, we expect mission-critical-minded customers to gravitate toward the Ebdsv5 family, described as delivering “higher remote storage performance in each VM size compared to Ev5 VM series.” The Ebdsv5 allows up to 260,000 IOPS and 8,000 MBps of remote disk storage throughput. Our approach was to find the “nearest neighbor” best fit. The challenge was selecting a balance of both CPU and memory. The E32bdsv5 on Azure offered a 32-core instance with 256 GiB of memory, the same as the r6idn.8xlarge. This was our best, most diligent effort at selecting compute hardware compatibility for our testing.

In terms of storage, our objective was to test both Azure Premium SSD v2 Disks and AWS General Purpose (gp3). For both Azure and AWS, we deployed multiple disks for SQL Server data and log files and combined them using Simple Storage Pools (RAID0 disk striping) with RHEL. Azure recommended striping the disks because of the design of the platform.

The Azure configuration had 117,920 total IOPS, and AWS had 87,500 total IOPS on the r6idn instance. With AWS gp3 drives, we arranged the disks to give the maximum allowed IOPS per instance, according to AWS documentation. We worked to employ equivalent configurations for these tests despite the different storage profiles between AWS and Azure.

Results may vary across different configurations, and again, you are encouraged to compile your own representative queries, data sets, data sizes, and test-compatible configurations applicable to your requirements. All told, our testing included two different database environments.

Table 1. Configurations Used for Tests

Other SQL Server settings include:

• Max degree of parallelism: 1
• Max server memory: 235,930 MB, which is 90% of total available system memory (256 GB)

GigaOm Analytic Field Test

The GigaOm Analytic Field Test is a workload derived from the well-recognized industry-standard TPC Benchmark™ H (TPC-H). Aspects of the workload were modified from the standard TPC-H benchmark for ease of benchmarking. As such, the results generated are not comparable to official TPC Results.

The setup for this Field Test was informed by the TPC-H spec validation queries. This is not an official TPC benchmark. The queries were executed using the following setup, environment, standards, and configurations.

Database Environments
The preceding table shows the configurations we used for the Analytic Field Test.

Other SQL Server settings include:

• Max degree of parallelism: 32
• Max server memory: 235,930 MB, which is 90% of total available system memory (256 GB)

Benchmark Data
The data sets used in the benchmark were data sets built from the well-recognized industry-standard TPC-H specification. Aspects of the workload were modified from the standard TPC-H benchmark for ease of benchmarking, and as such, the results generated are not comparable to official TPC Results.

From tpc.org: “The TPC-H is a decision support benchmark. It consists of a suite of business-oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database were chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.”

For more information about the TPC-H, see their specification document.

Queries
We sought to replicate the TPC-H benchmark queries modified only by syntax differences required by SQL Server. The benchmark is a fair representation of enterprise query needs. The TPC-H testing suite has 22 queries.

Test Execution
To execute the TPC-H benchmark queries, we ran the test sequence of Power Run, Power Run, Throughput Run. These were read-only queries for both Power and Throughput. A Power Run is a single user executing 22 queries in a serial stream. A Throughput Run is seven concurrent users, each executing a stream of 22 queries, giving 154 query executions with seven parallel streams. We completed each test sequence three times and took the best result.

Test Metrics
We used the Power and Throughput Runs to calculate the performance metrics of queries per hour (QPH). We used the longest running of the seven concurrent threads as the total execution time of the test. To calculate the QPH, we used the following formulas:

Power QPH = 3,600 seconds per hour / Geometric Mean of the 22 queries 
Throughput QPH = 8 streams x 22 queries / Throughput execution time (sec) x 3,600 seconds per hour
Total QPH = Square root of (Power QPH x Throughput QPH)

4. Field Test Results

Transactional Field Test Results

This section analyzes the transactions per second (tpsE) from the fastest of the five runs of the three GigaOm Transactional Field Tests described. A higher tpsE is better, meaning more transactions are processed every second.

The results of the TPC-E-inspired GigaOm Field Test show that Azure E32bds_v5 had greater performance than AWS r6idn, with 1,435 tpsE compared to 1,179 tpsE (a 22% advantage), as shown in Figure 1. This indicates that the Azure E32bds_v5 platform is more suited to handle high-performance mission-critical workloads, such as those involved in the TPC-E benchmark.

Figure 1. Transactions per Second: SQL Server 2022 Enterprise on RHEL Azure Virtual Machine’s Premium SSD v2 Managed Disks vs. AWS SQL Server 2022 Enterprise on RHEL EBS General Purpose SSD (gp3) (higher is better)

Analytic Field Test Results

This section analyzes the queries per hour (QPH) from the fastest of the five runs of the three GigaOm Analytic Field Tests described. Higher queries per hour is better, meaning more queries are processed every hour.

The TPC-H implementation is a read-only benchmark for data analytics.

The TPC-H-based results for SQL Server 2022 Enterprise on RHEL show that Azure E32bds_v5 has a higher best queries per hour (QPH) than AWS r6idn, with 367 QPH compared to 305 QPH (Figure 2). This suggests that Azure E32bds_v5 is more performant for these types of queries than AWS r6idn. The difference in performance is quite significant, with Azure E32bds_v5 providing a 20% performance improvement over AWS r6idn.

Figure 2. Analytic Queries per Hour (higher is better)

5. Price Per Performance

The price-performance metric is price/throughput (tpsE). This is defined as the cost of running each cloud platform continuously for three years divided by the transactions per second throughput uncovered in the previous tests. The calculation is as follows:

Price per Performance = $/tpsE =

[(Compute with on-demand SQL Server Hourly Rate × 24 hours/day × 365 days/year × 3 years)
+ (Data disk(s) monthly cost per disk × # of disks × 12 months × 3 years)
+ (Log disk monthly cost per disk × # of disks × 12 months × 3 years)] ÷ tpsE

When evaluating price per performance, the lower the number, the better. This means you get more compute power, storage I/O, and capacity for your budget.

Pricing Used:
We performed this calculation across two different pricing structures:

1. Azure pay-as-you-go (PAYG) versus AWS on-demand
2. Azure three-year reserved versus AWS standard three-year term reserved

The prices were at the time of testing. The compute prices include both the actual AWS EC2/Azure VM hardware itself and the license costs of the operating system and Microsoft SQL Server Enterprise Edition. We also included Azure Hybrid Benefit versus AWS License Mobility rates for existing SQL Server license holders.

Be aware that prices do not include support costs for either Azure or AWS. Each platform has different pricing options. Buyers should evaluate all their pricing choices, not just those presented in this paper.

Transactional Field Test Price-Performance

The results of the price-performance analysis of the TPC-E benchmark show that AWS r6idn provides a $360.32 price per transaction, whereas Azure E32bds_v5 provides a $291.84 price per transaction (Figure 3). That’s a 23% improvement with Azure. This reveals that Azure is more cost-effective than AWS, as it can process more transactions per second for the same amount of money. Additionally, Azure provides a significantly lower price-per-performance than AWS, making it the more cost-efficient cloud provider for this benchmark.

Figure 3. Price-Performance per Transactions per Second (lower is better)

The TPC-E results in Figure 4 show that Azure E32bds_v5 is the most cost-effective choice, with a price per performance of $69.70. AWS r6idn is less cost-effective at $89.94. Both solutions offer SQL Server license mobility, so organizations can easily move their licenses between cloud providers.

Figure 4. Price-Performance per Transactions per Second: AWS License Mobility/Azure Hybrid Benefit (lower is better)

The 3-year commitment results of the price-performance comparison between the two cloud providers for SQL Server 2022 Enterprise on RHEL show that AWS r6idn is slightly more expensive at $44.95, while Azure E32bds_v5 is the most cost-effective at $39.36–a difference of just over 14%. Figure 5 shows the results.

Figure 5. Price-Performance per Transactions per Second: AWS License Mobility/Azure Hybrid Benefit and 3-Year Commitment (lower is better)

Analytic Field Test Price-Performance

Starting with Figure 6, the focus shifts to exploring the price performance of Azure and AWS SQL Server deployments based on queries per hour.

The results show that AWS r6idn has a slightly higher price per performance than Azure E32bds_v5 for SQL Server 2022 Enterprise on RHEL PAYG/on-demand. Specifically, AWS r6idn has a price of $1,391.98, while Azure E32bds_v5 has a price of $1,140.44, as shown in Figure 6, a 22% difference. This suggests that the cost of using AWS r6idn is higher than that of using Azure E32bds_v5 in this scenario.

Figure 6. Price-Performance per Queries per Hour (lower is better)

The results show that AWS r6idn produced a 28% higher price per performance than Azure E32bds_v5 for the same TPC-H workload on SQL Server 2022 Enterprise running on RHEL with license mobility (Figure 7). This suggests Azure provides better value for money than AWS for this particular setup. Additionally, both solutions offer significant cost savings over traditional on-premises infrastructure.

Figure 7. Price-Performance per Queries per Hour: AWS License Mobility/Azure Hybrid Benefit (lower is better)

This analysis demonstrates that the price per performance for the AWS r6idn is significantly higher than that of the Azure E32bds_v5 for a 3-year commitment with SQL Server 2022 Enterprise on RHEL and license mobility. As shown in Figure 8, the AWS r6idn has a price per performance of $173.66, while the Azure E32bds_v5 has a price per performance of $153.81, nearly a $20 difference that indicates a 13% improvement for Azure over AWS. Therefore, Azure provides a more cost-effective option for this scenario.

Figure 8. Price-Performance per Queries per Hour: AWS License Mobility/Azure Hybrid Benefit and 3-Year Commitment (lower is better)

6. Conclusion

This report outlines the results from a GigaOm Transactional Field Test and a GigaOm Analytic Field Test to compare the same SQL Server infrastructure as a service (IaaS) offering of two cloud vendors: Microsoft SQL Server on Amazon Web Services (AWS) Elastic Cloud Compute (EC2) instances and Microsoft SQL Server on Azure Virtual Machines (VM) for RHEL.

We have learned that the database, cloud, and storage all impact latency, which is a killer for important transactional applications. Microsoft Azure offers a powerful cloud infrastructure for modern transactional and analytic workloads.

The results of the GigaOm Transactional Field Test show that Azure E32bds_v5 had 1,435 tpsE compared to 1,179 tpsE for AWS r6idn, indicating that the Azure platform is better suited for high-performance mission-critical workloads. Azure’s price per performance is $69.70, while r6idn is $89.94, yielding nearly a 30% efficiency advantage for Azure over AWS. Moreover, for a 3-year commitment, Azure E32bds_v5 is the most cost-effective choice at $39.36, while AWS r6idn is $44.95, respectively. In conclusion, Azure E32bds_v5 is the most cost-effective cloud provider for mission-critical workloads.

The analysis of the GigaOm Analytic Field Test results for SQL Server 2022 Enterprise on RHEL shows that Azure E32bds_v5 provides a higher best queries per hour (QPH) than AWS r6idn, with a significant 20% performance improvement.

The results indicate that for SQL Server 2022 Enterprise on RHEL PAYG/on-demand, AWS r6idn has a marginally higher price per performance than Azure E32bds v5. For the same TPC-H workload on SQL Server 2022 Enterprise running on RHEL with license mobility, AWS r6idn produced a 28% higher price per performance than Azure E32bds v5. Our analysis shows that for a 3-year commitment with SQL Server 2022 Enterprise on RHEL and license mobility, the price per performance for the AWS r6idn is significantly higher than that for the Azure E32bds v5.

Keep in mind that tests are configured to get the best from each platform according to publicly documented best practices. Optimizations on both platforms would be possible as their offerings evolve or internal tests point to different configurations.

7. About William McKnight

William McKnight is a former Fortune 50 technology executive and database engineer. An Ernst & Young Entrepreneur of the Year finalist and frequent best practices judge, he helps enterprise clients with action plans, architectures, strategies, and technology tools to manage information.

Currently, William is an analyst for GigaOm Research who takes corporate information and turns it into a bottom-line-enhancing asset. He has worked with Dong Energy, France Telecom, Pfizer, Samba Bank, ScotiaBank, Teva Pharmaceuticals, and Verizon, among many others. William focuses on delivering business value and solving business problems utilizing proven approaches in information management.

8. About Jake Dolezal

Jake Dolezal is a contributing analyst at GigaOm. He has two decades of experience in the information management field, with expertise in analytics, data warehousing, master data management, data governance, business intelligence, statistics, data modeling and integration, and visualization. Jake has solved technical problems across a broad range of industries, including healthcare, education, government, manufacturing, engineering, hospitality, and restaurants. He has a doctorate in information management from Syracuse University.

9. About GigaOm

GigaOm provides technical, operational, and business advice for IT’s strategic digital enterprise and business initiatives. Enterprise business leaders, CIOs, and technology organizations partner with GigaOm for practical, actionable, strategic, and visionary advice for modernizing and transforming their business. GigaOm’s advice empowers enterprises to successfully compete in an increasingly complicated business atmosphere that requires a solid understanding of constantly changing customer demands.

GigaOm works directly with enterprises both inside and outside of the IT organization to apply proven research and methodologies designed to avoid pitfalls and roadblocks while balancing risk and innovation. Research methodologies include but are not limited to adoption and benchmarking surveys, use cases, interviews, ROI/TCO, market landscapes, strategic trends, and technical benchmarks. Our analysts possess 20+ years of experience advising a spectrum of clients from early adopters to mainstream enterprises.

GigaOm’s perspective is that of the unbiased enterprise practitioner. Through this perspective, GigaOm connects with engaged and loyal subscribers on a deep and meaningful level.

10. Copyright

© Knowingly, Inc. 2023 "SQL Transaction Processing and Analytic Performance Price-Performance Testing" is a trademark of Knowingly, Inc. For permission to reproduce this report, please contact sales@gigaom.com.