Translate SQL Azure DTU to IOPS? [closed]
The closest I've been able to get to answering this question is an article from Microsoft titled Azure SQL Database Benchmark Overview. The writers performed a benchmark scenario on each one of the Azure SQL tiers and measured the results in terms of "Transaction per hour/minute/second".
Here's an image of the results in table form:
The latest version of the SQL Database Benchmark Overview article is more helpful by providing a number of IOPS per DTU, as follows:
- Basic and standard: 2.5 IOPS/DTU
- Premium: 48 IOPs/DTU
Therefore:
Tier DTUs IOPS-----------------------Basic 5 13 # Actually 12.5 IOPS.S0 10 25S1 20 50S2 50 125S3 100 250S4 200 1,000P1 125 6,000P2 250 12,000 # There is no P3 tierP4 500 24,000 # There is no P5 tierP6 1,000 48,000
I am the author of the Azure SQL Database Performance Testing blog posts mentioned above.
Making IOPS to DTU comparisons is quite difficult for Azure SQL Database, which is why I focussed on row counts and throughput rates (in MB per second) in my tests.
I would be cautious about using the Transaction Rates quoted by Microsoft - their benchmark databases are rather small e.g. for Standard tier, which has a capacity of 250 GB, their benchmark databases for S1 and S2 are only 2 GB and 7 GB respectively. At these sizes I suggest SQL Server is caching much/most of the database and as such their benchmark is avoid the worst of the read throttling that is likely to impact real world databases.
I have added a new post regarding the new Service Tiers hitting General Availability and making some estimates of the changes in performance around S0 and S1 at GA.
http://cbailiss.wordpress.com/2014/09/16/performance-in-new-azure-sql-database-performance-tiers/
Microsoft seems intentionally tight-lipped about the details of the Azure SQL Database benchmark process. I've emailed back and forth a bit with a MSFT representative who seemed competent but ultimately deflected or declined to answer my substantive questions.
I'm reading that as: there is no definitive way to convert a DTU measurement into anything absolute (i.e., anything useful).
Chris Baliss did a long series of posts with some actual performance tests. They may not be perfect, but it seems like the best we have right now as far as comparing the new SQL Database performance with anything else, in this case the old Web/Business model. Here's the first in his 15 post series:
You can see the final summary page here:
His general conclusions were that the old Business databases fell roughly between the new P1 and P2 in terms of performance, closer to P2 than P1. This is very discouraging considering the new plans will cost significantly more than the old ones - an order of magnitude increase, or more - unless you have an enormous database.