Microsoft has two main purchase model pricing options for a single Azure SQL Database. If you are going to use Azure SQL Database, you need to decide which purchase model you prefer, and then decide which service tier meets your performance and budget needs. This choice is not permanent, since it is pretty easy to migrate to a different service tier later if your needs change.
DTU Purchase Model
The older pricing option is the DTU-based SQL purchase model, where a fixed set of resources is assigned to the database from three performance tiers, which are Basic, Standard, and Premium.
For Standard and Premium, there are multiple service tiers, which are classified according to how many Database Transaction Units (DTUs) they provide (along with their included storage and maximum available storage). The Premium tier is designed for I/O intensive workloads, and is fault-tolerant.
The Database Transaction Unit (DTU) is based on a blended measure of CPU, memory, along with storage reads and writes. The DTU-based performance levels represent preconfigured bundles of compute, memory, and storage resources designed to drive different levels of application performance. If you do not want to worry about the underlying resources and prefer the simplicity of a preconfigured resource bundle while paying a fixed amount each month, you may find the DTU-based model more suitable for your needs and easier to understand.
Each service tier has different costs per hour, which also sometimes vary by geographic region. Tables 1 through 3 list the relevant performance statistics for each performance and service tier.
| Service Tier | DTUs | Included Storage | Maximum Storage |
|---|---|---|---|
| B | 5 | 2 GB | 2 GB |
Table 1: Basic Performance Tier
| Service Tier | DTUs | Included Storage | Maximum Storage |
|---|---|---|---|
| S0 | 10 | 250 GB | 250 GB |
| S1 | 20 | 250 GB | 250 GB |
| S2 | 50 | 250 GB | 250 GB |
| S3 | 100 | 250 GB | 1 TB |
| S4 | 200 | 250 GB | 1 TB |
| S6 | 400 | 250 GB | 1 TB |
| S7 | 800 | 250 GB | 1 TB |
| S9 | 1,600 | 250 GB | 1 TB |
| S12 | 3,000 | 250 GB | 1 TB |
Table 2: Standard Performance Tier
| Service Tier | DTUs | Included Storage | Maximum Storage |
|---|---|---|---|
| P1 | 125 | 500 GB | 1 TB |
| P2 | 250 | 500 GB | 1 TB |
| P4 | 500 | 500 GB | 1 TB |
| P6 | 1,000 | 500 GB | 1TB |
| P11 | 1,750 | 4 TB | 4 TB |
| P15 | 4,000 | 4 TB | 4 TB |
Table 3: Premium Performance Tier
vCore Purchase Model
The newer vCore purchasing model has two Performance tiers, which are General Purpose (GP) and Business Critical (BC). The two performance tiers mainly differ in their storage performance and high availability design.
Each performance tier has multiple service tiers, which are classified according to how many vCores are available, along with the amount of available RAM. Each Performance tier also lets you choose between Gen 4 CPUs, which use vCores based on Intel Xeon E5-2673 v3 (Haswell-EP) processors where each vCore is one physical processor core, or Gen 5 CPUs, which use vCores based on newer Intel Xeon E5-2673 v4 (Broadwell-EP) processors where each vCore is one logical processor core.
Both of these processors are special, “bespoke” models, typically used by cloud providers such as Microsoft. Gen 4 uses the Intel Xeon E5-2673 v3, which has 12 physical cores, with a base clock speed of 2.4 GHz and a Turbo clock speed of 3.2 GHz and a 30 MB L3 cache. Gen 5 uses the newer Intel Xeon E5-2673 v4, which has 20 physical cores, with a base clock speed of 2.3 GHz and a Turbo clock speed of 3.5 GHz and a 50 MB L3 cache.
The vCore counts and available memory for each performance and service tier (for both Gen 4 and Gen 5 CPUs) are detailed in Tables 4 through 7. These Intel processors only work in two-socket host servers. This means that a Gen 4, 24 vCore Service Tier is using all of the processor cores in the host, while a Gen 5, 80 vCore Service Tier is also using all of processor cores in the host. There won’t be any larger service tiers with Gen 4 or Gen 5 CPUs!
| vCORE | Memory (GB) |
|---|---|
| 1 | 7 |
| 2 | 14 |
| 4 | 28 |
| 8 | 56 |
| 16 | 112 |
| 24 | 164 |
Table 4: General Purpose, Gen 4 Physical CPU Cores
| vCORE | Memory (GB) |
|---|---|
| 2 | 11 |
| 4 | 22 |
| 8 | 44 |
| 16 | 88 |
| 24 | 132 |
| 32 | 176 |
| 48 | 264 |
| 80 | 440 |
Table 5: General Purpose, Gen 5 Logical CPU Cores
| vCORE | Memory (GB) |
|---|---|
| 1 | 7 |
| 2 | 14 |
| 4 | 28 |
| 8 | 56 |
| 16 | 112 |
| 24 | 168 |
Table 6: Business Critical, Gen 4 Physical CPU Cores
| vCORE | Memory (GB) |
|---|---|
| 2 | 11 |
| 4 | 22 |
| 8 | 44 |
| 16 | 88 |
| 24 | 132 |
| 32 | 176 |
| 48 | 264 |
| 80 | 440 |
Table 7: Business Critical, Gen 5 Logical CPU Cores
According to my estimated TPC-E score calculations, both of the largest available service tiers for each CPU generation have roughly equivalent single-threaded CPU performance, while the Gen 5 has more total CPU capacity due to its using two, 20-core CPUs in the host machine.
The comparative estimated TPC-E numbers are detailed in Table 8.
| CPU Gen / Service Tier | Total Estimated TPC-E Score | Estimated Score/Logical Core |
|---|---|---|
| Gen 4, 24 vCore | 2624.06 | 54.67 |
| Gen 5, 80 vCore | 4489.22 | 56.12 |
Table 8: Estimated TPC-E Scores
These numbers would be the same for both the General Purpose and Business Critical Performance tiers, which have identical processor and memory configurations at each service tier level. Where the General Purpose and Business Critical performance tiers differ is in their I/O performance.
General Purpose uses premium remote storage rated at 500 IOPS per vCore (with a 7,000 IOPS max) while Business Critical uses local SSD storage rated at 5000 IOPS per vCore (with a 200,000 IOP max). Gen 4 offers slightly a higher memory density per vCore (7 GB per vCore) and a total memory capacity of 168 GB, while Gen 5 offers 5.5 GB per vCore, with a higher total memory capacity of 440 GB.
I think it is needlessly confusing how Microsoft decided to count physical vCores for Gen 4 hardware and logical vCores for Gen 5 hardware, especially since the underlying Intel Xeon E5 v3 and E5 v4 processors both support hyper-threading and the same amounts of memory. Despite this, I think the new vCore Purchase Model is a useful option that gives you more choices and flexibility. An added benefit of vCore pricing over DTU pricing is that you can use the Azure Hybrid Benefit to transfer on-premises licenses to Azure SQL Database.
So what did MS do in Gen4 – Xeon E5 v3 systems? I recall in the Xeon E5/7 v2 that certain SKUs had HT disabled, I made the mistake of buying those. Most of the public E5 2600 series v3 have HT, except the 2 low-end.
Did MS disable HT, or is the vCPU the physical core with both LPs?
I am not sure why you show 54.67 per core for E5 v3. The only TPC-E I am aware of is 3,772.08 for 36 core/72 threads for 52.39 per core.
Two more items.
I will assume that if you get half of the vCores of the whole system, then all cores are on the same physical socket, as opposed to split across both, and that memory is allocated locally.
In this case, all memory access is local,
versus cores and mem on 2S, in which case TPC-E does 80% local node access. Still, if all cores and memory are single socket, there might be a 15% bump in per core.
Second. On a VM, performance should be de-rated by 3-4% because the memory address translation is slightly more complicated than on a physical machine.
The server vendors only submit official TPC-E scores for the "flagship", highest core count processor of each generation processor family. You are correct that the only official TPC-E score for the Xeon E5-2600 v3 family was 3772.08 for a Xeon E5-2699 v3 (36 core/72 threads) for 52.39/ logical core.
To get an estimated TPC-E score for a different processor from the same generation and family, I simply adjust for the difference in core counts and the difference in the base clock speed (since most of the cores will be running at base clock speed most of the time during a TPC-E benchmark).
In this example, 3772.08 (the flagship score) * 0.667 (core count adjustment) * 1.04 (base clock speed adjustment) = 2616.62 as an estimated TPC-E score for the E5-2673 v3. Dividing that by 48 logical cores gives a score/core of 54.51. It turns out that I made a small arithmetic mistake in my figures in the article for that processor.
If we wanted to be more precise, we could also look at the L3 cache size, the L3 cache size per core, and the QPI speed. Ideally, the server vendors would submit more TPC-E scores beyond just the flagship processor.
I totally agree that a single-socket system would not have any NUMA overhead. The newer E5-2673 v4 processor should have slightly better VM performance because of some virtualization performance improvements in Broadwell-EP.
See this document: https://intel.ly/2dUCXEU
For small operations with staff that must wear different hats and cannot delve intro every detail. Is there an easy way to compare DTU vs Vcores in Gen 4 or Gen 5?
Also do you happen to know what is the difference in physical core vs the logical and why Gen5 memory allocation is less than in Gen 4.
Azure obfuscates needlessly their services.