In a previous blog I talked about the new Esv5 Azure VM Types that looked very promising for SQL Server and data warehousing , perhaps unseating the older DSv2 that we had been recommending.
https://prodata.ie/2021/11/03/the-new-king-vm-type-for-sql-has-arrived/
Now that we have upgraded a couple of customers from the 8 or 16 core DS to the equivalent Esv5 we can share some of the performance gains.
TDLR Summary
We see about a 33% performance increase. Eg faster run time on ETLs and SQL Queries for a lower cost and more resources like ram, network and local SSD.
So the business case is a slam dunk. I would upgrade every DSv2 to the new ESv5 right now, obviously doing dev=>test->prod on critical workloads.
Side by Side Comparison
Below is a side by side comparison of performance features between the two VM Types with comments below (for an 8 core VM)
- While the CPU Clock speed is significantly higher and the chipset is newer with Turbo Boost enabled (all positive signs) the VM is hyperthreaded which typically is a very negative sign. So its not immediately clear if this will give better compute – Hence the need for real world workload testing.
- The 8 core has 8GB additional ram and the 16 core 16GB. It doesn’t mention the speed of the ram, but I think its fair to say that its newer/faster ram and there is more of it.
- The Ev5 series offers less max uncached disk throughput than the older DSv2 series. Something that we would be very concerned about with a SQL DW workload doing lots of table scans. However, it also offers a new feature of “burst” throughput of up to 1,200 MB/Sec on an 8 core.
- Network bandwidth is more than doubled. We rarely peak above 1Gbit, so hard to see this in action, but nothing to be sniffed at.
- The massively larger Local SSD is welcome. I was just running out of tempdb space on one customer site and got a bit nervous when someone said “can we just make the drive bigger”
| Property | DS13v2 | E8ds_v5 |
|---|---|---|
| ACU | 210-250 | TBA |
| CPU Specs | 8 Core 2.1 to 2.3 Ghz | 8 Core Hyperthreaded Up to 3.5Ghz |
| Memory (GB) | 56 | 64 |
| Temp Storage (SSD) | 112 | 300 |
| Max Uncached Throughput | 384 | 290 |
| Max Burst Throughput | N/A | 1,200 |
| Max NIC Bandwidth | 6,000 | 12,500 |
| Price (Euro /Month) Base VM Excluding SQL with Windows 2019 | 598.37 | 620.53 |
Below is some performance test on real world data warehouse workloads.
ETL Run Times
The nightly ETL load uses a mixture of SSIS, OPENROWSET and SProcs to extract a few 100GB of data and transform/load the data in a star schema.
Load Times went form 122 minutes to 82 minutes. So about 33% faster.
SProc Run Times
I took a complex SProc that took 23 minutes to run on the DSv2 and on the new ESv5 (same #cores) the run time was about 35% faster with same dataset at 15 minutes.
Cost Difference
There is a small cost saving in going from the DSv2 to the ESv5. Prices will vary depending on agreements, but using public pay as you go on compute a comparison is below
- DSv2 €1,267.47
- Esv5 €1,215.31
Note that the Esv5 comes with an interesting option to not have the local SSD for an additional cost savings. We find that useful for workloads that don’t need the D: drive SSD.
Conclusion
So far, upgrading to the ESv5 has been the easiest business case I have ever seen. Better, faster, newer, cheaper and just a flick of a switch.