We have been experiencing significant performance challenges with running SQL Server on Microsoft Azure using IAAS based virtual machines. In investigation the differences in performance, we ran some performance tests using a tool called HammerDB which allows for easy simulation of the TPC-C industry standard benchmark (It can simulate any other other SQL script you like as a test as well). You can simulate a number of virtual test users who will hammer your SQL database with your test scripts and measure the results.
Based on the standard TPC-C benchmark, here are some interesting results.
Running on My Laptop
My laptop is a Dell Precision M4700 with 32 GB of RAM loaded with SQL Server 2012 Enterprise. It has 4 physical cores so it should be able to handle easily 4 virtual users with its own dedicated core. Here is the test running with 4 virtual users.
As you can see by the performance counters, the limiting factor is clearly I/O – the disk hits 100% while 4 users banging at 4 cores barely hits 13% of available CPU. Memory is also ready available with 69% free.
Here are the results of the test in transactions per minute:
As you can see by the graph, my laptop generates approx. 40K transactions per minute.
SQL 2012 on Azure
In order to test out Azure SQL performance, we set up a VM using the SQL Server 2012 SP2 Enterprise Optimized for Transactional Workloads.
We then setup Hammer DB using the same test script to install the TPC-C database. Here are the results of the test on SQL 2012.
Again, the challenge isn’t CPU or RAM, it’s I/O.
SQL 2014 on Azure
SQL 2014 provides about the same performance of SQL 2012 out of the box. Note that the database itself is the same SQL as used on the SQL 2012 server, so perhaps adding in some in memory optimizations could improve the performance. Here are the results with SQL 2014.
Conclusion: It’s all about I/O
The key conclusion is that as expected with databases, it’s all about I/O. If we look at just the basic speed at which SQL can write to disk, there is a massive difference between my local laptop (which has a very standard SATA drive in it) and what Azure disk volumes are delivering, even when using supposedly optimized file storage settings.
My laptop is writing at 4 MB per second while the Azure VMs are only writing at less than 1 MB per second.
