Translate SQL Azure DTU to IOPS?

Question

The resources and power of each service tier and performance level for Microsoft's Azure SQL cloud databases are expressed in terms of Database Throughput Units (DTUs).

These are relative units of measurement. Is there any way to translate them into an absolute measurement? Specifically can they be translated into IOPS?

Solution 1

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/

Solution 2

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         25
S1        20         50
S2        50        125
S3       100        250
S4       200      1,000
P1       125      6,000
P2       250     12,000   # There is no P3 tier
P4       500     24,000   # There is no P5 tier
P6     1,000     48,000

Solution 3

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:

• http://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-background-and-overview/

You can see the final summary page here:

• http://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-summary/

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.

Comments

[Calvin Fisher]

This thread also has some useful and frank discussion: social.msdn.microsoft.com/Forums/sqlserver/en-US/…

[Calvin Fisher]

I got a quotable recommendation out of my MSFT rep: "The recommendation is to use of the $200 trial to stand up an Azure SQL Test db (ideally a migration of a db on their existing SQL Server instance) and benchmark thoroughly."

[Paul Brewer]

A virtual server with 4 GB RAM and 2 VCPU's equates roughly to Performance Level 'S3'. A physical server with 12 GB RAM, 1 processor with 4 cores equates roughly to 'P1'. The DTU Calculator Service accurately recommends a performance level - paulbrewer.wordpress.com/2015/12/03/…

[Paul Brewer]

The DTU Calculator factors in the performance of a give SQL Server AND the workload. Exactly the same workload may yield different recommendations for different servers. It calculates which Performance Level will process the workload in similar times to the current on-premise host, it's very good in my opinion - dtucalculator.azurewebsites.net

[Zapnologica]

I like what you say here. Because I have a large database 200GB table with 500 million rows. and the simplest of queries like count(*) on indexed table can take 2 hours to execute using 100 DTU with the data component being the majority. Where as my smaller db is fast.

[eRunner]

I found your table and I was glad for it to be so clear. But I see now in the document you pointed out, they replaced '2.5 IOPS per DTU' with '1-5 IOPS per DTU' for the Basic and Standard tiers. Does that mean IOPS go from DTUs * 1 to DTUs * 5 in this two tiers? What does that mean? They also replaced '48 IOPS per DTU' with '25 IOPS per DTU' on the Premium so I guess IOPS in this case should go down to almost half?