Table of Contents
In terms of transactional data, relational databases are still the platform of choice for most organizations and most use cases. Arguably, the most prevalent relational database engine over the past couple of decades is Microsoft SQL Server. Since 1989, the use of Microsoft SQL Server has been widely adopted for day-in-day-out On-Line Transaction Processing (OLTP) and many other uses. It has proven itself to be a useful and powerful database, and we see it underpin a variety of operational and reporting processes in companies under every industry vertical.
Today, with cloud computing becoming more and more prevalent, SQL Server is even easier to deploy and use. A SQL Server can be quickly stood up on infrastructure offered as a service, taking complete advantage of the cloud. SQL Server Infrastructure as a Service (IaaS) cloud offerings provides predictable costs, cost savings, fast response times, and strong non-functionals.
But does it matter which public cloud? If you deploy SQL Server on one cloud provider’s infrastructure versus another, is there a significant difference? We sought to answer the question does one cloud vendor’s infrastructure better support SQL Server than another? We conducted some testing recently to see if this were the case.
Since Microsoft SQL Server is offered on both AWS and Azure, we desired to see if deploying on Azure gives SQL Server a better infrastructure foundation for transactional processing. We closely aligned the hardware configuration between both clouds as reasonably as possible. This is a very difficult task to assure sameness and fairness across configurations.
To test this hypothesis, we conducted a GigaOm Transactional Field Test, derived from the industry-standard TPC Benchmark™ E (TPC-E), compared:
- Microsoft SQL Server 2019 on an Amazon Web Services (AWS) r5a.8xlarge Elastic Cloud Compute (EC2) instance with General Purpose (gp2) volumes
- Microsoft SQL Server 2019 on an Azure E32as_v4 Virtual Machine (VM) with P30 Premium Storage drives
Both the AWS R5a and Azure Eas v4 are the latest release instance types. The r5a.8xlarge and E32as_v4 instances both have 32 vCPUs and 256GB of RAM. Both setups were installations of Microsoft SQL Server 2019 running on Windows Server 2019 Datacenter Edition.
With the Azure feature of local cache, Microsoft SQL Server on Microsoft Azure Virtual Machines (VM) indicated 3.6x more transactional throughput on Windows over Microsoft SQL Server on Amazon Web Services (AWS) Elastic Cloud Compute (EC2
By using the transaction-based price-performance formula, SQL Server on Microsoft Azure Virtual Machines (VM) had up to 84.2% better price-performance when comparing Azure Hybrid Benefit to AWS License Mobility for three-year reservations, and up to 71.6% better price-performance when comparing the Azure pay-as-you-go pricing to Amazon on-demand rates.
Testing hardware and software across cloud vendors is very challenging. Configurations 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 itself. Our testing demonstrates a narrow slice of potential configurations and workloads.
As the sponsor of the report, Microsoft selected the particular Azure configuration it desired to test. GigaOm selected the AWS instance configuration that was closest in terms of CPU and memory configuration.
We leave the issue of fairness for the reader to determine. We strongly encourage you, as the reader, to look past marketing messages and discern for yourself what is of value. We hope this report is informative and helpful in uncovering some of the challenges and nuances of platform selection.
Also, in the same spirit of the TPC, price-performance is intended to be a normalizer of performance results across different configurations.
The parameters to replicate this test are provided. We used the BenchCraft tool, which was audited by a TPC-approved auditor, who has also reviewed all updates to BenchCraft. All the information required to reproduce the 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 did change the number of Initial Trading Days (ITD). The default value is 300, which is the number of 8-hour business days to populate the initial database. For these tests, we used an ITD of 30 days rather than 300. This reduces the size of the initial database population in the larger 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 obtained would not be compliant with the TPC-E specification and, therefore, not 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.
We have provided enough information in the report for anyone to reproduce this test. You are encouraged to compile your own representative queries, data sets, data sizes, and test compatible configurations applicable to your requirements.
2. The Latest IaaS Offerings for SQL Server
The two major public cloud vendors, Azure and AWS, offer many infrastructure choices for SQL Server. They even provide fully-managed deployments of SQL Server where you do not have to touch the underlying infrastructure, and everything is managed for you, automating many cumbersome database administration tasks. We are not testing the fully-managed offerings, because we want to be more selective with our infrastructure configuration, as we believe many SQL Server customers migrating from on-premises to the cloud will also desire.
The focus of our testing is some of the latest and greatest Azure virtual machine series and AWS EC2 instance families. We also chose ones that had very similar CPU counts and RAM allocations.
Microsoft desired to test its Eas_v4 series, which was released for general availability in late 2019. The Eas_v4 series are powered by AMD EPYC™ 7452 processors. Like its competitor, we chose AWS’ latest as of the testing date, the AWS EC2 R5a family, which was first introduced to 18 operational geographic regions in late 2018 and rolled out to the additional regions in 2019. The R5a instances are powered by custom AMD EPYC™ processors running at 2.5 GHz, which are at least the same “make and model” as the Azure Eas_v4. The R5a also scale up with the same memory increments as the Eas_v4, so it made as good as a configuration similarity as we could have hoped for.
However, there is a major difference between SQL Server on Azure and SQL Server on AWS in terms of storage configuration—and as our testing shows, this has downstream Input/Output (I/O) performance impacts.
Azure Virtual Machines Storage Options
For operationally-intensive, business-critical workloads, Azure has Premium Managed Disks—a Solid-State Drive (SSD) disk type for general purpose use. It is an Azure “middle of the road” offering between Standard Disks and its high-end Ultra Disk.
Premium Managed Disks are high-performance SSDs designed to support I/O intensive workloads and provide high throughput and low latency. Premium SSD 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 Managed Disks depends on the number and size of the disks selected, as well as by the number of outbound data transfers. These disk sizes provide different Input/output Operations Per Sec (IOPS), throughput (MB/second), and monthly price per GiB. Several persistent disks attached to a VM can support petabytes of storage per VM. Premium four disk configurations can achieve up to 80,000 IOPS and 1,600 MB per second disk throughput per VM—which translates to less than one millisecond latency for read operations with applications which can take advantage of read caching. Premium SSD Managed Disks are supported by DS-series, FS-series, and GS-series VMs. The largest single disk is the P80 with 32TB of storage, IOPS up to 20,000, and up to 900 MB per second of throughput.
Two unique configuration options offered with Azure Premium Managed Disks (that AWS does not currently offer) are local ReadWrite and Read-only cache. The local cache is a specialized component that stores data, typically in memory, so that it can be accessed more quickly. Read cache attempts to reduce read latency, while with write cache, data to be written to permanent storage is queued in the cache. This feature is not available on all disk types, nor is it available for temporary storage disks. Also, not all applications can leverage cache. According to Microsoft:
Caching uses specialized, and sometimes expensive, temporary storage that has faster read and write performance than permanent storage. Because cache storage is often limited, decisions need to be made as to what data operations will benefit most from caching. But even where the cache can be made widely available, such as in Azure, it’s still important to know the workload patterns of each disk before deciding which caching type to use.
Standard Hard Disk Drive (HDD) Managed Disks are also offered to save on cost. Azure customers can store Managed Disk snapshots and images from Premium SSD Managed Disks on Standard HDD storage. You can choose between Locally Redundant Storage (LRS) and Zone Redundant Storage (ZRS) options.
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 broad range of workloads, such as relational and non-relational databases, enterprise applications, containerized applications, big data analytics engines, and file systems. With EBS, AWS customers can choose from four different volume types to balance optimal price and performance. You can achieve single-digit, millisecond-latency for high-performance database workloads.
Amazon EBS also offers a good general-purpose solid-state drive type—General Purpose SSD (gp2). The Gp2 disk type seems comparable to Azure Premium Disks. For higher performance, you would choose AWS Provisioned IOPS SSD (io1), like you might choose Ultra Disk on Azure.
General Purpose SSD (gp2) volumes balance price and performance for a wide variety of workloads. AWS recommends this drive type for most workloads. However, with an IOPS ceiling of only 16,000, it tends to underperform unless relegated to system boot volumes, virtual desktops, low-latency applications, and development/test environments.
One of our main objectives in this benchmark is to test an I/O intensive workload on Amazon and Azure’s “middle-tier” SSD volume types head-to-head—to understand both the performance and price-per-performance differences of the two leading cloud vendor’s SQL Server offerings. Again, AWS does not offer the local cache feature available in Azure, and we desired to see the difference in performance when Read-only cache was enabled on Azure data disks compared to AWS without the benefit of the local cache.
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). The workload was slightly modified, i.e., transaction mix, from the standard TPC-E benchmark for ease of benchmarking, and 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 be broadly representative of 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 the 50 foreign key constraints. The results of TPC-E are valuable to 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 Benchmark™ E (TPC-E) specification. For this testing, we used the database scaled for 800,000 customers. This 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: 1,382,400,000. All of the other tables were scaled according to the TPC-E specification and rules. On our Azure virtual machines and AWS EC2 instances, we allocated 4TB of storage—which was more than enough for this workload. Besides the scale factor of 800,000 customers, the test offers a few other “knobs” we turned in order to determine the database engine’s maximum throughput capability for both AWS and Azure. See Table 1.
We completed five runs per test on each platform. Each test run lasted at least two hours each. We then took the average transactions per second for the last 30 minutes of the test runs. Also, a full backup was restored to reset the database back to its original state between each run. The results are shared in the Field Test Results section.
Selecting and sizing the compute and storage for comparison can be challenging, particularly across two different cloud vendors’ offerings. There are various offerings between AWS and Azure for transaction-heavy workloads. As you will see in Table 2, at the time of testing and publication, there was not an exact match across the offerings in neither processors nor memory.
As previously discussed, we considered the variety of offerings on AWS and selected the memory-optimized R5a family. On the Azure side, we expect mission-critical-minded customers to gravitate towards the Easv4 family which is described as “ideal for memory-intensive enterprise applications.” We opted for the Eas_v4 series, which offers premium storage disks.
The goal was selecting a balance of both CPU and memory. The r5a.8xlarge and E32as_v4 instances both have 32 vCPUs and 256GB of RAM. Both setups were installations of Microsoft SQL Server 2019 running on Windows Server 2019 Datacenter Edition. This was our best, most diligent effort at selecting a congruent configuration for our testing.
In terms of storage, our initial objective was to test a single 4TB data disk of both Azure Premium Disks and AWS General Purpose (gp2). However, Read-only cache on Azure was not available for a volume size that large, so instead, we deployed four (4) 1TB disks, each with Read-cache enabled and combined them using Storage Pool on Windows. Striping is not required to enable Caching for a disk on Azure—caching is available for a single disk. Striping the disks was recommended to us by Azure because of the design of the platform. Azure Premium Storage is offered in pre-fixed sizes and IOPS caps. Thus, striping of equal size disks is the publicly documented and a recommended best practice to achieve the desired size/IOPS configuration. AWS gp2 storage is not offered in this way. A customer can provision a single disk of any size. This theoretically eliminates the need for striping of multiple disks on gp2. However, we striped four (4) 1TB volumes anyway, to create as much “sameness” as possible.
NOTE: Any chance of data loss (a potential downside to RAID0 disk striping) of SQL Server is recommended to be protected by having an asynchronous Disaster Recovery replica or by restoring from a backup. There are other functional differences between the two databases as well.
NOTE: Another configuration difference that may have impacted our results was that the Azure virtual machine had locally-attached temporary storage for the SQL Server “tempdb” database. The SQL Server tempdb stores internal objects created by the database engine such as work tables for sorts, spools, hash joins, hash aggregates, and intermediate results. Having the tempdb on local temporary storage usually means higher I/O performance. The AWS R5a instance type we used does not have locally attached temporary storage; thus, the tempdb was stored, by default, on the operating system disk. However, the TPC-E workload does not have large or complicated sorts or joins. Hence, it would be speculative to say with certainty the impact of this without a deep analysis of tempdb activity for this particular workload.
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. For more information on storage type and IOPs, please reference the footnote.
For these tests, we tested the platforms with standard “out-of-the-box” SQL Server configurations, that is, no additional or special configurations were made than what came installed on our SQL Server machines.
4. Field Test Results
This section analyzes the transactions per second (tps) from the fastest of the five runs of each of the three GigaOm Transactional Field Tests described above. A higher tps is better—meaning more transactions are being processed every second.
5. Price Per Performance
The price-performance metric is price/throughput. This is defined as the cost of running each of the cloud platforms continuously for three years divided by the transactions per second throughput uncovered in the previous tests 1-3. The calculation is as follows:
Price Per Performance = $/tps = <br>
[(Compute with on-demand SQL Server license Hourly Rate × 24 hours/day × 365 days/year × 3 years)
+ (Data disk(s) monthly cost per TB × 4 TB × 12 months × 3 years)
+ (Log disk monthly cost per TB × 1 TB × 12 months × 3 years)] ÷ tps
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.
We performed this calculation using Azure pay-as-you-go rates versus AWS on-demand rates. The prices below were at the time of testing and reflect the Oregon region on AWS and West US 2 region on Azure. 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. Please note that significant discounts can be applied, including year to multi-year commitments as well as Azure Hybrid Benefit and AWS License Mobility rates for existing SQL Server license holders.
Note: Prices do not include support costs for either Azure or AWS.
Each platform has different pricing options. Buyers should evaluate all of their pricing choices, not just the ones presented in this paper.
This report outlines the results from a GigaOm Transactional Field Test to compare the same SQL Server infrastructure as a service (IaaS) offering or two cloud vendors: Microsoft SQL Server on Amazon Web Services (AWS) Elastic Cloud Compute (EC2) instances and Microsoft SQL Server Microsoft on Azure Virtual Machines (VM).
With this particular configuration and this particular workload, Microsoft SQL Server on Microsoft Azure Virtual Machines (VM) showed 3.6x better performance on Windows than Microsoft SQL Server on Amazon Web Services (AWS) Elastic Cloud Compute (EC2). By using the transaction-based price-performance formula, SQL Server on Microsoft Azure Virtual Machines (VM) had up to 84.2% better price-performance when comparing Azure Hybrid Benefit to AWS License Mobility for three-year reservations, and up to 71.6% better price-performance when comparing the Azure pay-as-you-go pricing to Amazon on-demand rates.
We have learned that the database, along with the cloud, and the storage, matters to transactional latency. Microsoft SQL Server Azure presents a powerful cloud infrastructure offering for the modern transactional workload by offering enhanced storage options—such as disk read caching. The TPC-E-like workload we chose is more read-intensive than TPC-C. Thus, on this day for this particular workload with these particular configurations, SQL Server ran faster on Azure than AWS.
Keep in mind, 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 offering evolves or internal tests point to different configurations.
Performance is important but it is only one criterion for a business-critical database platform selection. This test is a point-in-time check into specific performance. There are numerous other factors to consider in selection across factors of Administration, Integration, Workload Management, User Interface, Scalability, Vendor, Reliability, and numerous other criteria. It is also our experience that performance changes over time and is competitively different for different workloads. Also, a performance leader can hit up against the point of diminishing returns and viable contenders can quickly close the gap.
The benchmark setup was informed by the TPC Benchmark™ E (TPC-E) specification. The workload was derived from TPC-E and is not an official TPC benchmark nor should the results be compared to official TPC-E publications.
GigaOm runs all of its performance tests to strict ethical standards. The results of the report are the objective results of the application of queries to the simulations described in the report. The report clearly defines the selected criteria and process used to establish the field test. The report also clearly states the data set sizes, the platforms, the queries, etc. used. The reader is left to determine for themselves how to qualify the information for their individual needs. The report does not make any claim regarding the third-party certification and presents the objective results received from the application of the process to the criteria as described in the report. The report strictly measures performance and does not purport to evaluate other factors that potential customers may find relevant when making a purchase decision.
This is a sponsored report. Microsoft chose the competitors, the test, and the Microsoft configuration. GigaOm chose the most compatible configurations for the other tested platform and ran the testing workloads. Choosing compatible configurations is subject to judgment. We have attempted to describe our decisions in this paper.
9. About Microsoft
Microsoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.
Microsoft offers SQL Server on Azure. To learn more about Azure SQL Virtual Machines visit https://azure.microsoft.com/en-us/services/virtual-machines/sql-server/.
10. 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.
11. 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.
© Knowingly, Inc. 2020 "SQL Transaction Processing, Price-Performance Testing" is a trademark of Knowingly, Inc. For permission to reproduce this report, please contact firstname.lastname@example.org.