Comparing the new SQL Azure tiers to old ones [closed]

Question

Now that Microsoft made the new SQL Azure service tiers available (Basic, Standard, Premium) we are trying to figure out how they map to the existing ones (Web and Business).

Essentially, there are six performance levels in the new tier breakdown: Basic, S1, S2, P1, P2 and P3 (details here: http://msdn.microsoft.com/library/dn741336.aspx)

Does anyone know how the old database tiers map to those six levels? For instance, is Business equivalent of an S1? S2?

We need to be able to answer this question in order to figure out what service tiers/levels to migrate our existing databases to.

Answer 1

We just finished a performance comparison.

I can't publish our SQL queries, but we used 3 different test cases that match our normal activity. In each test case, we performed several queries with table joins and aggregate calculations (SUM, AVG, etc) for a few thousand rows. Our test database is modest - about 5GB in size with a few million rows.

A few notes: For each, we tested the my local machine which is a 5 year old iMac running Windows/SQL Server in a virtual machine ("Local"), SQL Azure Business ("Business"), SQL Azure Premium P1, SQL Azure Standard S2, and SQL Azure Standard S1. The basic tier seemed so slow that we didn't test it. All of these tests were done with no other activity on the system. The queries did not return data so network performance was hopefully not a factor.

Here were our results:

Test One

Local: 1 second
Business: 2 seconds
P1: 2 seconds
S2: 4 seconds
S1: 14 seconds

Test Two

Local: 2 seconds
Business: 5 seconds
P1: 5 seconds
S2: 10 seconds
S1: 30 seconds

Test Three

Local: 5 seconds
Business: 12 seconds
P1: 13 seconds
S2: 25 seconds
S1: 77 seconds

Conclusions:

After working with the different tiers for a few days, our team concluded a few things:

• P1 appears to perform at the same level as SQL Azure Business. (P1 is 10x the price)
• Basic and S1 are way too slow for anything but a starter database.
• The Business tier is a shared service so performance depends on what other users are on your server. Our database shows a max of 4.01% CPU, 0.77% Data IO ,0.14% Log IO and we're experiencing major performance problems and timeouts. Microsoft Support confirmed that we are "just on a really busy server."
• The Business tier delivers inconsistent service across servers and regions. In our case, we moved to a different server in a different region and our service is back to normal. (we view that as a temporary solution)
• S1, S2, P1 tiers seem to provide the same performance across regions. We tested West and North Central.
• Considering the results above, we're generally worried about the future of SQL Azure. The business tier has been great for us for a few years, but it's scheduled to go out of service in 12 months. The new tiers seem over priced compared to the Business tier.

I'm sure there are 100 ways this could be more scientific, but I'm hoping those stats help others getting ready to evaluate.

UPDATE:

Microsoft Support sent us a very helpful query to assess your database usage.

SELECT
       avg(avg_cpu_percent) AS 'Average CPU Percentage Used',
       max(avg_cpu_percent) AS 'Maximum CPU Percentage Used',
       avg(avg_physical_data_read_percent) AS 'Average Physical IOPS Percentage',
       max(avg_physical_data_read_percent) AS 'Maximum Physical IOPS Percentage',
                   avg(avg_log_write_percent) AS 'Average Log Write Percentage',
       max(avg_log_write_percent) AS 'Maximum Log Write Percentage',
       --avg(avg_memory_percent) AS 'Average Memory Used Percentage',
       --max(avg_memory_percent) AS 'Maximum Memory Used Percentage',
       avg(active_worker_count) AS 'Average # of Workers',
       max(active_worker_count) AS 'Maximum # of Workers'
FROM sys.resource_stats
WHERE database_name = 'YOUR_DATABASE_NAME' AND
start_time > DATEADD(day, -7, GETDATE())

The most useful part is that the percentages represent % of an S2 instance. According to Microsoft Support, if you're at 100%, you're using 100% of an S2, 200% would be equivalent to a P1 instance.

We're having very good luck with P1 instances now, although the price difference has been a shocker.

Answer 2

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/

Answer 3

There is not really any kind of mapping between the old and new offerings near as I can tell. The old offerings the only thing that was really different between the "web" and "business" offering was the size the database was limited to.

However, on the new offerings each tier has performance metrics associated with them. So in order to decide what offering you need to move your existing databases to you need to figure out what type of performance needs your application has.