Cost and performance is likely going to be your key considerations when placing your SQL Server on Azure.
Microsoft provides the following Performance guidelines for SQL Server in Azure Virtual Machines and SQL server best practices to optimize performance in Azure Stack documents which give a comprehensive overview of configuring SQL Server on Azure VMs.
As I’ve been working quite a bit on Azure lately, I thought I’d list a few of my findings.
Picking a Virtual Machine
At the time of this blog, there were 172 VM sizes to choose from.
The key considerations for a SQL Server VM relate to:
- CPUs (Potentially impacts license costs)
- Memory (The more memory the better as much faster than disk)
- Disk capacity (Sufficient disks space to store your databases/backups)
- Disk speed (Impacts data read/write access performance)
So with this in mind we can narrow down suitable VMs.
If the goal is to get a SQL Server 2017 Standard Edition VM with 4 vCPUs (minimum core license requirement) and 128GB RAM (Edition limit) we end up with a E16-4s_v3.
The E16-4s_v3 is a constrained core size VM (see below) which has 4 vCPUs, 128GB memory and up to 25,600 IOPS.
Constrained core size VMs allow for a reduced vCPU count for licensing while keeping the RAM and MAX IOPS unchanged for the same price.
In Microsoft’s documentation they refer to use of DS2_v2 or higher for Standard Edition and DS3_v2 or higher for Enterprise Edition.
If the goal is to obtain a VM with maximum disk IOPS (currently 80,000) while keeping SQL license costs down you’ll probably be best to go with a E64-16s_v3 which has 16 vCPUs and 432GB RAM.
VM Disks
Once you have decided on your VM, the next most important step is deciding on the disks to add to the VM.
The table above shows the max throughput available for the disk, but depending on your VM you may not achieve that throughput due to Microsoft setting IOPS limits on the various VMs.
I’ll demonstrate these limits with the widely used CrystalDiskMark tool to place load on various disk configurations.
Below shows CrystalDiskMark run against a very old $100 Apacer AS681 SSD 2.5 7mm SATAIII, 240GB drive which is no longer sold.
All tests were run 3 times using a 1GB file to save time.
- Seq Q32T1: Sequential, Queues = 32, Threads = 1
- 4KiB Q8T8: Random 4KiB, Queues = 8, Threads = 8
- 4KiB Q32T1: Random 4KiB, Queues = 32, Threads = 1
- 4KiB Q1T1: Random 4KiB, Queues = 1, Threads = 1
All tests made use of the recommended Storage Spaces with Simple Resiliency and the number Columns (think of it as a stripe) equaling the number of disks. The disks were formatted using best practice NTFS 64K allocation unit size and GPT partition to allow exceeding 2TB. Simple Resiliency is all that is required due to the underlying Azure infrastructure ref: “Locally redundant storage (LRS) replicates your data three times within a single data center. LRS provides at least 99.999999999% (11 nines) durability of objects over a given year.”
- E16-4s_v3: Has 4 vCPUs, 128GB RAM and Max IOPS 25,600
- E32-8s_v3: Has 8 vCPUs, 256GB RAM and Max IOPS 51,200
- E64-16s_v3: Has 16 vCPUs, 432GB RAM and Max IOPS 80,000
Host Caching set to None
| E16-4s_v3 | E32-8s_v3 | E64-16s_v3 | |
| Read / Write (MB/s) | Read / Write (MB/s) | Read / Write (MB/s) | |
| 1 x P30: Seq Q32T1 | 204 / 205 | 204 / 206 | 204 / 206 |
| 1 x P30: 4KiB Q8T8 | 21 / 20 | 20 / 20 | 20 / 21 |
| 1 x P30: 4KiB Q32T1 | 21 / 21 | 20 / 20 | 21 /21 |
| 1 x P30: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 /1 |
| Cost per month NZ$ 204.37 (Size 1TB) | |||
| 2 x P30: Seq Q32T1 | 395 / 393 | 411 / 409 | 411 / 409 |
| 2 x P30: 4KiB Q8T8 | 41 / 41 | 41 / 41 | 41 / 41 |
| 2 x P30: 4KiB Q32T1 | 40 / 40 | 38 / 39 | 39 / 40 |
| 2 x P30: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 408.74 (Size 2TB) | |||
| 3 x P30: Seq Q32T1 | 392 / 393 | 613 / 612 | 612 / 612 |
| 3 x P30: 4KiB Q8T8 | 61 / 62 | 61 / 62 | 61 / 62 |
| 3 x P30: 4KiB Q32T1 | 41 / 47 | 40 / 43 | 41 / 45 |
| 3 x P30: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 613.11 (Size 3TB) | |||
| 4 x P30: Seq Q32T1 | 394 / 394 | 783 / 780 | 815 / 811 |
| 4 x P30: 4KiB Q8T8 | 80 / 81 | 80 / 81 | 80 / 81 |
| 4 x P30: 4KiB Q32T1 | 42 / 47 | 42 / 45 | 42 / 45 |
| 4 x P30: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 817.48 (Size 4TB) | |||
| 5 x P30: Seq Q32T1 | 392 / 394 | 783 / 778 | 1006 / 896 |
| 5 x P30: 4KiB Q8T8 | 84 / 97 | 83 / 88 | 87 / 93 |
| 5 x P30: 4KiB Q32T1 | 42 / 47 | 41 / 45 | 42 / 45 |
| 5 x P30: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 1,021.85 (Size 5TB) | |||
| 1 x P40: Seq Q32T1 | 255 / 257 | 257 / 256 | 255 / 257 |
| 1 x P40: 4KiB Q8T8 | 31 / 31 | 31 / 31 | 31 / 31 |
| 1 x P40: 4KiB Q32T1 | 31 / 31 | 31 / 31 | 31 / 31 |
| 1 x P40: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 391.67 (Size 2TB) | |||
| 1 x P60: Seq Q32T1 | 392 / 394 | 513 / 511 | 510 / 511 |
| 1 x P60: 4KiB Q8T8 | 66 / 67 | 66 / 67 | 66 / 67 |
| 1 x P60: 4KiB Q32T1 | 51 / 59 | 46 / 51 | 46 / 54 |
| 1 x P60: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 1,430.42 (Size 8TB) | |||
| 1 x P70: Seq Q32T1 | 393 / 394 | 765 / 764 | 766 / 764 |
| 1 x P70: 4KiB Q8T8 | 75 / 75 | 75 / 75 | 75 / 75 |
| 1 x P70: 4KiB Q32T1 | 51 / 58 | 46 / 52 | 45 / 59 |
| 1 x P70: 4KiB Q1T1 | 1 / 1 | 1 / 1 | 1 / 1 |
| Cost per month NZ$ 2,724.62 (Size 16TB) | |||
As shown above, there is clearly VM throttling in place and you could possibly be throwing money away by trying to use faster and/or more disks to get better IO performance.
Host Caching set to Read-only
| E16-4s_v3 | E32-8s_v3 | E64-16s_v3 | |
| Read / Write (MB/s) | Read / Write (MB/s) | Read / Write (MB/s) | |
| 1 x P30: Seq Q32T1 | 269 / 205 | 538 / 205 | 1073 / 206 |
| 1 x P30: 4KiB Q8T8 | 133 / 21 | 268 / 21 | 534 / 20 |
| 1 x P30: 4KiB Q32T1 | 134 / 21 | 269 / 21 | 470 / 21 |
| 1 x P30: 4KiB Q1T1 | 19 / 1 | 54 / 1 | 58 / 1 |
| Cost per month NZ$ 204.37 (Size 1TB) | |||
| 2 x P30: Seq Q32T1 | 269 / 268 | 536 / 408 | 1071 / 408 |
| 2 x P30: 4KiB Q8T8 | 134 / 41 | 269 / 41 | 535 / 41 |
| 2 x P30: 4KiB Q32T1 | 134 / 38 | 269 / 39 | 489 / 40 |
| 2 x P30: 4KiB Q1T1 | 20 / 1 | 54 / 1 | 61 / 1 |
| Cost per month NZ$ 408.74 (Size 2TB) | |||
| 3 x P30: Seq Q32T1 | 269 / 268 | 539 / 535 | 1075 / 611 |
| 3 x P30: 4KiB Q8T8 | 134 / 61 | 267 / 61 | 535 / 62 |
| 3 x P30: 4KiB Q32T1 | 134 / 35 | 268 / 42 | 463 / 44 |
| 3 x P30: 4KiB Q1T1 | 19 / 1 | 54 / 1 | 58 / 1 |
| Cost per month NZ$ 613.11 (Size 3TB) | |||
| 4 x P30: Seq Q32T1 | 270 / 269 | 540 / 535 | 1073 / 812 |
| 4 x P30: 4KiB Q8T8 | 134 / 72 | 267 / 81 | 536 / 81 |
| 4 x P30: 4KiB Q32T1 | 134 / 37 | 268 / 44 | 481 / 44 |
| 4 x P30: 4KiB Q1T1 | 20 / 1 | 52 / 1 | 60 / 1 |
| Cost per month NZ$ 817.48 (Size 4TB) | |||
| 5 x P30: Seq Q32T1 | 268 / 270 | 538 / 535 | 1074 / 877 |
| 5 x P30: 4KiB Q8T8 | 134 / 66 | 269 / 89 | 535 / 90 |
| 5 x P30: 4KiB Q32T1 | 134 / 36 | 268 / 45 | 477 / 46 |
| 5 x P30: 4KiB Q1T1 | 18 / 1 | 54 / 1 | 60 / 1 |
| Cost per month NZ$ 1,021.85 (Size 5TB) | |||
| 1 x P40: Seq Q32T1 | 269 / 256 | 540 / 256 | 1075 / 256 |
| 1 x P40: 4KiB Q8T8 | 134 / 31 | 270 / 31 | 535 / 31 |
| 1 x P40: 4KiB Q32T1 | 134 / 31 | 268 / 31 | 496 / 31 |
| 1 x P40: 4KiB Q1T1 | 19 / 1 | 35 / 1 | 61 / 1 |
| Cost per month NZ$ 391.67 (Size 2TB) | |||
| 1 x P60: Seq Q32T1 | No read cache available | ||
| 1 x P60: 4KiB Q8T8 | No read cache available | ||
| 1 x P60: 4KiB Q32T1 | No read cache available | ||
| 1 x P60: 4KiB Q1T1 | No read cache available | ||
| Cost per month NZ$ 1,430.42 (Size 8TB) | |||
| 1 x P70: Seq Q32T1 | No read cache available | ||
| 1 x P70: 4KiB Q8T8 | No read cache available | ||
| 1 x P70: 4KiB Q32T1 | No read cache available | ||
| 1 x P70: 4KiB Q1T1 | No read cache available | ||
| Cost per month NZ$ 2,724.62 (Size 16TB) | |||
There are a lot of ways to interpret the data above, but key findings included:
- Host caching set to None, can offer faster performance than Read-only cache.
- The Larger sized disks do not offer caching.
- Good option for SQL Server when a low number of disks are allocated to the VM.
Host Caching set to Read/write
| E16-4s_v3 | E32-8s_v3 | E64-16s_v3 | |
| Read / Write (MB/s) | Read / Write (MB/s) | Read / Write (MB/s) | |
| 1 x P30: Seq Q32T1 | 269 / 221 | 538 / 451 | 1080 / 792 |
| 1 x P30: 4KiB Q8T8 | 134 / 80 | 268 / 156 | 535 / 403 |
| 1 x P30: 4KiB Q32T1 | 134 / 87 | 269 / 117 | 470 / 239 |
| 1 x P30: 4KiB Q1T1 | 18 / 26 | 54 / 39 | 58 / 51 |
| Cost per month NZ$ 204.37 (Size 1TB) | |||
| 2 x P30: Seq Q32T1 | 269 / 270 | 538 / 440 | 1076 / 1077 |
| 2 x P30: 4KiB Q8T8 | 134 / 133 | 269 / 194 | 535 / 504 |
| 2 x P30: 4KiB Q32T1 | 133 / 134 | 269 / 127 | 489 / 388 |
| 2 x P30: 4KiB Q1T1 | 19 / 36 | 49 / 30 | 61 / 31 |
| Cost per month NZ$ 408.74 (Size 2TB) | |||
| 3 x P30: Seq Q32T1 | 269 / 270 | 539 / 535 | 1075 / 806 |
| 3 x P30: 4KiB Q8T8 | 134 / 129 | 268 / 266 | 535 / 524 |
| 3 x P30: 4KiB Q32T1 | 133 / 66 | 268 / 267 | 463 / 386 |
| 3 x P30: 4KiB Q1T1 | 19 / 24 | 49 / 45 | 58 / 41 |
| Cost per month NZ$ 613.11 (Size 3TB) | |||
| 4 x P30: Seq Q32T1 | 268 / 250 | 536 / 540 | 1070 / 1023 |
| 4 x P30: 4KiB Q8T8 | 134 / 68 | 267 / 202 | 536 / 530 |
| 4 x P30: 4KiB Q32T1 | 134 / 123 | 267 / 193 | 481 / 370 |
| 4 x P30: 4KiB Q1T1 | 19 / 27 | 49 / 44 | 60 / 40 |
| Cost per month NZ$ 817.48 (Size 4TB) | |||
| 5 x P30: Seq Q32T1 | 269 / 261 | 539 / 519 | 1070 / 1020 |
| 5 x P30: 4KiB Q8T8 | 134 / 133 | 268 / 267 | 536 / 531 |
| 5 x P30: 4KiB Q32T1 | 134 / 134 | 268 / 201 | 481 / 374 |
| 5 x P30: 4KiB Q1T1 | 19 / 39 | 49 / 38 | 60 / 41 |
| Cost per month NZ$ 1,021.85 (Size 5TB) | |||
| 1 x P40: Seq Q32T1 | 269 / 270 | 539 / 246 | 1079 / 993 |
| 1 x P40: 4KiB Q8T8 | 133 / 133 | 270 / 231 | 535 / 530 |
| 1 x P40: 4KiB Q32T1 | 133 / 71 | 268 / 55 | 484 / 409 |
| 1 x P40: 4KiB Q1T1 | 19 / 26 | 35 / 23 | 59 / 57 |
| Cost per month NZ$ 391.67 (Size 2TB) | |||
| 1 x P60: Seq Q32T1 | No caching available | ||
| 1 x P60: 4KiB Q8T8 | No caching available | ||
| 1 x P60: 4KiB Q32T1 | No caching available | ||
| 1 x P60: 4KiB Q1T1 | No caching available | ||
| Cost per month NZ$ 1,430.42 (Size 8TB) | |||
| 1 x P70: Seq Q32T1 | No caching available | ||
| 1 x P70: 4KiB Q8T8 | No caching available | ||
| 1 x P70: 4KiB Q32T1 | No caching available | ||
| 1 x P70: 4KiB Q1T1 | No caching available | ||
| Cost per month NZ$ 2,724.62 (Size 16TB) | |||
- Microsoft does not recommend used of Read/write cache for SQL Server.
VM Temporary Drive
|
E16-4s_v3 (256GB Size) |
E32-8s_v3 (512GB Size) |
E64-16s_v3 (864GB Size) |
|
| Read / Write (MB/s) | Read / Write (MB/s) | Read / Write (MB/s) | |
| Seq Q32T1 | 270 / 270 | 538 / 535 | 1067 / 1074 |
| 4KiB Q8T8 | 134 / 134 | 268 / 266 | 476 / 489 |
| 4KiB Q32T1 | 133 / 133 | 248 / 238 | 277 / 271 |
| 4KiB Q1T1 | 15 / 22 | 20 / 29 | 21 / 30 |
- The temporary drive throughput appears to reflect throttled premium disks with read/write cache enabled.
To summarize:
Review your VM’s temporary (D) drive performance and size to gauge where your tempdb database should be placed.
Having several striped disks does not guarantee that you will get faster throughput, rather this is dependent on the VM used.
To get faster IO performance you need a lot of disk space (even with the new Ultra disks), which you may / may not require, and remember you get charged for the whole storage (not just what you use) plus IOs (except temporary drive).
If you down scale a VM, you should ensure that the VM supports the number of drives you currently have attached and it you are using the temporary (D) drive, that the size will be sufficient for your tempdb files.
When using Storage Spaces, if you need to add additional capacity, you’ll need to add the same number of disks e.g. a 3 disk virtual drive will require an additional 3 disks. Also when the new disks are added, the Column count does not increase, so although you have increased capacity, you don’t increase the disk performance.
Depending on your SQL Server requirements, the cloud is not always cost effective.
