Performance of Web Database seems quicker than new Azure SQL DB service tiers?

Question

I am using MVC3, EF5, LINQ, .NET4.5, SQL Database.

Microsoft has just brought out the new service levels for SQL Databases ie Basic, Standard and Premium.

Originally I was using the "Web" SQL database since my DB was small ie about 30mb. However on my test web site instance I have been using Basic web site and "Basic" SQL Database setups to save money.

I have a "slower" running query which suddenly took 9secs when my Live DB was restored as a "Basic" new style DB on the test instance. It tool about 2.5 secs on live. When I scaled up this test DB instance to "Standard" SO, 20 DTUs, it took 3.9 secs. When I then scaled this DB back to the "retired" "Web" format, it then took 1.9 secs which really surprised me. It is as if one needs to scale the DB to S1 to get comparable performance to the old "Web" style DB, but I suspect this will then cost more than the old "Web" format DB.

I appreciate any comments on the above, especially if other have found the new DB styles can be slower.

At the end of the day, what setup in the new DB style is the old "Web" style equivalent to?

Thanks.

EDIT (THIS IS REALLY REALLY WORRYING)

I have discovered a very useful document on this, and my worst fears are confirmed see Web/Business comparison with new SQL Database service tiers. These are very, very worrying as it seems that web database performance can only be matched by the "Premium P1" edition, and we would not be able to afford the use of this. So for the time being we will continue to use the "Web" edition.

EDIT, Seem to have touched a raw nerve.... There are many worried folks about this....

see: Forum chat with worried users

FEEDBACK FROM .NET USER GROUP

I have also been speaking with a number of my Azure using .NET peers at a recent user group meeting, and they were also very worried to the extend they believed developers would just leave Azure. I think one of the key mistakes here, by Microsoft, is to set the performance of Basic well below that of Web(most of the time) and even S1 and S2 below web. It is only when you get onto P1 and P2 that you experience a par, and we dare not use this in test due to the impact on charges. In our experience Web has performed at this high level for 90% of the time. I am guessing the 10% is there, since you say it is, but non of our clients have complained about this. However to retain our current level of performance we would need to upgrade to S2 or P1 which would have an extraordinary impact on our monthly charges. Jim Rand's feedback is appreciated, and backs up our concerns.

Answer 1

I am the author of the blog post mentioned above. A more up to date version of that post is available: http://cbailiss.wordpress.com/2014/09/16/performance-in-new-azure-sql-database-performance-tiers/

The tests I conducted were primarily around the physical I/O capabilities of the new service tiers. From those tests I believe that P1 offers roughly the same I/O on average as Web/Business.

So, the specific answer to your question:

At the end of the day, what setup in the new DB style is the old "Web" style equivalent to?

If you were running toward the physical I/O limits of Web/Business (roughly speaking 200MB+ read, 50MB+ write per minute), then I would say a minimum of P1 is needed to offer equivalent I/O performance in the newer service tiers.

If on average your I/O is generally much less than the figures above, then the database may perform OK on one of the Standard Tiers.

My tests didn't quantify/compare CPU or memory differences between Web/Business and the new tiers, but they too scale by service tier in the new world. The sys.resource_stats DMV in the master database might offer some insight for your workload. See the newer blog post above for more details.

For completeness, it is worth mentioning that the newer service tiers do offer some other advantages likely supporting more connections concurrently, new availability features, new backup features, etc.

Hope that helps...

EDIT: Jan 2015: A new Standard S3 performance level is currently in preview as part of the Azure SQL Database v12 version. This looks like it will offer price-performance at a point much closer to Business Edition than has been available until now. In addition, every service tier and performance level looks to be gaining higher performance in v12. See my blog post for details: https://cbailiss.wordpress.com/2014/12/17/azure-sql-database-v12-performance-tests-show-significant-performance-increase/

Chris

Answer 2

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Hit this last Thursday. Converting data from old system to SQL Azure. Chose the new Standard (S2) instead of the 5 gig web (retired) database. The SQL:

UPDATE Invoice 
SET SalesOrderID = O.SalesOrderID 
FROM Invoice 
INNER JOIN SalesOrder AS O ON Invoice.InvoiceID = O.InvoiceID 

196043 rows. Re ran and it took over 4 minutes. Exported database and reloaded it into the web edition. Query took 19 seconds. Total database size is about 750 megabytes.

Bottom line, this is more than "all a little worrying". Unless Microsoft gets the performance up on the new basic / standard / premium tiers to where it is now in the web edition, they can pretty much kiss Azure goodbye. Totally unreasonable that you can't run a query on only 196043 rows unless the the data is in the cache. So much for analytics with a relational database.

I'll be advising my client this week of this matter. Undoubtedly, he will be contacting upper management at Microsoft.

Answer 3

Jim, I'd be happy to help. We know that changing business models is a hard thing to do. In the Web/Business case, you pay on size of the DB and you get whatever performance we have at the time. Sometimes this is great, other times this is ok and sometimes performance is very poor. Customers have given us feedback that this unpredictable performance is very difficult to deal with.

Using this feedback as a key input, the business model for Basic/Standard/Premium is $/perf. Understanding what resources your consuming is a great first step before moving to B/S/P. We have several pieces of new guidance that should help you do this

http://azure.microsoft.com/en-us/documentation/articles/sql-database-upgrade-new-service-tiers/ Your mileage may vary here. Many customers see a decrease because of this business model change. Others see no impact, and some will see an increase if their DBs are very small and consume a lot of resources. I and the team would be happy to help customers move into the new business model. To have great conversations will need some customer specifics that aren't best shared in a public forum. guyhay@microsoft is my email if you'd like to have that conversation.