Comparative Analysis of Performance Tiers in SQL Azure

1
votes

Can someone explain, generically or otherwise, what the differences are between the various performance tiers in SQL Azure?

One thing I have learned is that SQL Azure IS NOT the same thing as a local instance of SQL Server when considering a basic installation of the latter. For example, we currently stored binary data in the database in SQL Azure. Deleting a 50MB blob from our SQL Azure database takes roughly 80 seconds. In the local instance of SQL Server, it takes only a few seconds. In this case, it seems that the Log IO is what is being hammered. I have been told that the data is getting replicated multiple times (I am assuming this is for redundancy reasons). Further, I have been told that if I were to configure my local environment the exact same way that SQL Azure is configured (hardware, replication, etc.) that I would see similar performance.

Back to the original question. What is the performance level of, say, S3 comparable to? What I am trying to figure out is how to explain why this performance tier seems to work so poorly at times when I frequently hear something to the effect of "we have SQL Server running on a really slow machine and it outperforms SQL Azure". I want to be able to compare the two to really understand why the differences exist.

2
sql-serverazure-sql-database
SQL Azure is relatively slow, but most performance issues I find are more due to app performance, for example is your login page making 100 sql calls. Comparing different levels in SQL Azure is not easy, a Standard with 200 DTU is order of magnitude slower than Premium with 125 DTU. Once my app is optimised I just try them manually until I find a tier with acceptable performance. – Craig

2 Answers

1
votes

If you are storing BLOBs of 50 MB on Azure SQL Database and you are seeing high Log IO, then it seems your workload is I/O intensive. Premium tiers are intended for that kind of workload. If you know the IOPS required by your databases it may help you know that a S3 provides a performance of 150 IOPS more or less. A P4 provides a performance of up to 4000 IOPS. This article may help you understand what DTU means and decide the appropriate tier for your workload.

Please allow me to make you the recommendation of storing your BLOB objects in Azure BLOB Storage and store only metadata of those objects in Azure SQL Database to lower the current demands of I/O of the database. Storing BLOB objects on Azure SQL Database will increase the size of the database and may prompt scaling up tiers since the database may soon reach the storage limit of the current tier. Azure BLOB storage is more scalable and is cheaper to store BLOB there. If you don't store BLOB on the database then you can use Standard tiers and save money instead of using Premium tiers.

1
votes

To assess the performance of a service tier for Azure SQL Database, there are two things to consider.

The first one is the number of DTUs (Database Transaction Units) associated with your service tier. DTUs measure the amount of resources available to your database. They mix CPU, IO and memory. The exact formula is described in this blog post. The larger the DTU count allocated to your database, the faster it will run queries.

The second factor is about hardware performance bottlenecks. Premium service tiers run on machines with higher IO and larger memory than Standard service tiers. As a result, if the performance of your application is mostly dependent on IO, you will experience significant gains by moving to Premium regardless of the amount of DTUs.