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.
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.
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”
|CPU Specs||8 Core|
2.1 to 2.3 Ghz
|8 Core Hyperthreaded|
Up to 3.5Ghz
|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
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.
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.
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.