Microsoft Azure SQL Database: vCore vs. DTU

We love Microsoft Azure. It is by far the most developed cloud providers in IaaS, PaaS, and even SaaS (Office 365) that you can get. It is well thought out from an administration point of view, a strong API to control most aspects of it, and offers as many developer-oriented services as you can imagine. Much like Amazon, it also offers its variety of databases as a service.

Database as a Service (DBaaS)

Buying a license for database software and installing it on a server is only a small portion of the cost and effort. Since most database licenses are not cheap to begin with, you can imagine the total bill, which includes items such as:

• Physical and Virtual Server or Servers
• Operating System Licenses
• Backup Devices
• Monitoring Services/Servers
• Maintenance and Administration Staff
• High Availability Replication Servers and Databases

Of course, then there is the real work to make sure that the database itself is performing, is secure, and is replicated properly by testing restore and recovery procedures and so forth. Microsoft Azure currently offers a wide array of database as a service offerings, as does Amazon with their RDS offerings among others.

Why Microsoft Azure SQL Database?

ClicData runs user data exclusively on MS Azure SQL database because it is simply the most advanced and well-performing database in the market. It is not the cheapest but not the most expensive one either. Some of the following features either make it unique or are definitively better implemented than the competition:

• Dynamic memory caches
• Columnar store indexes
• Transparent data encryption

And for our specific case, because we use mostly .NET and Azure technology, selecting this database also makes sense from a technology stack point of view, since all products are Microsoft made. Finally, we also wanted a DBaaS provider that covers as many countries as possible. As each region and country starts enforcing stronger privacy and data location legislation, we want to make sure that the data resides as close as possible to our customers.

Database Performance and Sizing

Database sizing and configuration is not easy. It requires a lot of iterations of testing and monitoring to fine tune a database, with different hardware and under different workloads, schema, and volume. A query that performs well under a certain hardware configuration may not perform as well in another, even if the configuration is perceived as better.

Azure's database offers are even more complex to navigate. Because their revenue model, infrastructure, and scaling are not transparent to the customers, their offers use abstract scales to measure performance. These scales are not easily aligned with real-world examples. Basically, today they offer two main modes: a DTU based model and a vCore based model.

DTU Model

According to Microsoft, a Database Transaction Unit or DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. Doubling the DTUs by increasing the performance level of a database equates to doubling the set of resources available to that database. Their first line of database offerings in this model is basic, standard, and premium.

They are offered under a DTU model where you scale the DTU higher if you need more performance. They can all be targeted for production use, but what is different about them is the IO. In basic and standard, the IOPS per DTU is 2.5, whereas in Premium, it is a whopping 48 IOPS per DTU.

And here lies the first issue. Since the DTU is defined as a measure of data, and log I/O and IOPS is defined as an input/output performance measurement used to characterize computer storage devices like hard disk drives, solid state drives, and storage area networks, they should be related or at least impacting one and the other. However, looking at the pricing of a standard database, for example, an S3 is 100 DTUs and a P1 is 100 DTUs. So should they perform the same or not?

If the IOPS on premium are higher, we would assume that they don't, but since a DTU is a "blended measure that includes IO," we would assume that they do — otherwise, what is the point of coming up with a scale that actually doesn't measure performance? More on this later...

vCore Model

Earlier this year, Microsoft also released the vCore series. The vCore series is supposed to be better since you can select the size of the disk and the number of vCores for that database. vCore comes in 2 flavors: general purpose and business critical. Additionally, they have 2 generations of processors/configurations: generation 4 and generation 5.

General purpose boasts 500 IOPS per vCore to a maximum 7000, while business critical is 5000 per vCore up to a maximum of 200,000 IOPS. vCore seems like a much more reasonable model to size databases but has its own inconsistencies.

Taking general purpose as an example, after 14 cores, you are at the maximum IOPS possible. Because GP is sold in steps of 8, 16, 24, 32, 40, 64, and 80 vCores, in essence, it means that after 8 cores, you are at the maximum IOPS possible. So, in what cases would you need so much parallelism in your queries that requires 80 vCores when the real bottleneck will be your disk?

I am sure there are queries that can profit from that, but not in many cases. Additionally, selecting generation 4 or generation 5 in both general purpose and business critical sometimes gives different values in both the Azure Portal as well as the pricing calculator.

Confused? Test. Test. Test.

We became really confused as to what to select for our customers. We know what type of queries we run, but it was difficult for us to bank on one specific series. We are still running mostly on DTU, but the vCore series seems like a better fit for our customers. We then took it upon ourselves to do our own performance testing and compare as many series and models as possible.

Test Criteria

Remember that a configuration built for one use case may not be ideal in another use case, so we ran 2 major distinct uses cases; bulk loading of data and read aggregated/non-aggregated data. Keep that in mind that we don't do a lot of UPDATES and DELETES in our databases. Also keep in mind that we apply Columnar Stored Indexes on almost all columns on each table and that our aggregates are software generated.

Loading: Our loading tests involved 2 tables, one with 10 Million rows exactly and 5 columns of varying types and the other with just over 18 Million rows and 15 columns, also of varying types and lengths. We measured loading the data sets every 20 minutes and every 1 hour respectively for 2 days straight on each selected Azure configuration. One of the loads was done in concurrency with other loads to further test if additional cores or type of disk performed better under multiple data load scenarios. 

Querying: For querying, we picked 3 resource intensive queries based on actual production use of ClicData and simulated them using our own test data. These queries involved calculations and aggregations at all times since that is our number one case scenario. One of them included a window function as well.

The Results

Using our own dashboards, we monitored all the tests as we changed the same account to use different database configurations. The dashboard below shows a partial rendition of the monitoring dashboard, each dot representing the time take on that particular test run to perform that task. In the background, you will see the database configuration in use at that time.

We then grouped all results into one summary dashboard as follows:

This dashboard was very useful to do relative comparisons between configurations and understand if our queries increased in performance across the different configurations. Some special notes:

1. GP vs. BC and Gen4 vs. Gen5

This was one of the most interesting items to compare since with GP, we are able to have larger storage and with BC, we get Solid State drives increasing performance 8x to 10x, on read. Take a look at the vCore 4 for BC/GP and Gen4/Gen5 comparison in our tests.

Does the above make sense? BC costs 3-4x more than GP, yet we had identical results except for the latest generation Gen5. Remember that this is not 1 test or 10. These are the aggregated results of 2 days of tests running every 5 minutes (queries) or every hour (loading). Is this an outlier? More on this later.

2. DTU vs. vCore

When it comes to selecting between the vCore and DTU models, we were interested in identifying what is the equivalent for us between them. As such, we took the vCore 4 in GP and BC and selected the best approximate DTU configurations. Roughly, both BC and GP Gen4 4 Vcores perform slightly better than a P4 or an S7. However, notice once more a possible outlier that S7 performs substantially better in query execution. Selecting the S4 would not be a wise decision, but since there is no middle ground between S4 and S7, it is one or the other.

Unexplained Outliers

As shown above, there some inconsistent behaviors. One of the most critical ones we found was a consistent performance increase from vCore 1 to 8, but on vCore 16, it was worse than a vCore 6! We did not understand the difference between Gen4 and Gen5 and why Gen5 (the new generation supposedly) performed worse in some cases.

We also noticed that no further performance could be gained on the same query, meaning the queries could not perform better even if the configuration was increased in core or disk type. This was applicable to both DTU and vCore and makes sense even for bare metal SQL server installations. It basically gets to a point that SQL server must synchronously perform an operation and that it can't optimize any further.

Performance Is One Thing. Cost Is Another.

Before we finish, let's talk about cost. If there is one negative thing about Microsoft Azure it's their billing and cost monitoring. So, of course, we built our dashboard that breaks down what the cost per basic component is per database. We use this to monitor all our infrastructure across all Microsoft data regions.

By using a dashboard like this, you can get a handle on what major areas are costing the most. It's clear to us that our platform uses mostly SQL Database (green) but also storage with VMs and App Service after.

Summary

After using Azure for over 6 years, we have learned a lot, and our most recent batch of tests should be interesting to those using Microsoft SQL Azure and wanting to know more about the differences between the tiers and configurations. Hope this helps!