Glenn Berry

Azure SQL Database Performance and Service Tiers Explained

August 1, 2018 by in Azure, SQL Performance | 2 Comments
Free eBook : Query Optimization
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts

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.