Azure SQL: Performance benchmarks between S0, S1, S2 tier and P1, P2, P3 Tiers?

4
votes

We have a few legacy databases in the Auzre cloud that are in the older pricing tiers ('Business' - before the Basic / Standard / Premium tiers became available).

We are looking to do a transition over to the new structures (and take advantage of the new features) - and as a result ran a few tests come to an understanding of how much of a performance increase we would get for the pricing structures.

The Azure SQL versions are measured in Database Throughput Units (DTUs), and Azure attributes these units directly to increases in performance: A DTU represents the power of the database engine as a blended measure of CPU, memory, and read and write rates. This measure helps a customer assess the relative power of the six SQL Database performance levels available across Basic, Standard and Premium (Basic, S1, S2, P1, P2, and P3). Please visit the performance guide on MSDN for more information.

After not seeing any difference between an S0 and S2 database - we stood up a P3 version to test as a benchmark against the S0. We were surprised to find absolutely no performance difference between running a fairly complex query against the S0 and P3 Azure SQL databases.

Both instances were build from a backup file from the original Business tier Azure SQL file (Could this be an issue?)

I am at a loss - as I expected that the P3 database (800 DTU units) was going to blow away the S0 database (10 DTU units). Both performed almost identically. Both also used the same execution plan, and both had the indexes in place (DBA set those up - not myself).

Any insights as to why we would have not seen a difference in these configurations?

Anyone else have any examples of increased performance or performance distinctions across these tiers?

2
sql-serverazureazure-sql-database
This is an excellent question. Did you manage to come to any conclusions about this? – MEMark
This blog post (and earlier posts on the same blog) may be helpful: cbailiss.wordpress.com/2014/12/17/… – cbailiss
Without details about the benchmark this question is unanswerable. – usr

2 Answers

2
votes

Is it possible that your query runs well within the resource limits provided by S0 database itself and hence higher level DBs are not helping you any further? Best way to monitor this is to measure the DTU percentage utilization of your DB when the query is run. If your DTU utilization is < 100% for S0, you may not benefit further from higher levels of DB editions for that query.

Here is a blog that provides more information on monitoring resource usage and appropriately sizing your database performance levels. http://azure.microsoft.com/blog/2014/09/11/azure-sql-database-introduces-new-near-real-time-performance-metrics/

-- Srini

1
votes

I have performed a series of performance tests against Azure SQL Database.

Back in July 2014 I performed a quite extensive series of tests. See results here: https://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-summary/

My most recent tests involved comparing the generally available v11 version with the newer v12 (currently in preview). This shows some significant performance improvements: https://cbailiss.wordpress.com/2014/12/17/azure-sql-database-v12-performance-tests-show-significant-performance-increase/

As to why you have seen no performance difference between the different service tiers I am not sure. There are very large differences between the resources available in each service tier and performance level. In all my tests Premium Tier offers performance well above that of Standard Tier. What was the test you were running (i.e. what was the SQL and what was the table structure, data volume, etc?) and what were your results (e.g. time taken)?