Azure SQL: benchmarks between S0, S1, S2 and P1, P2, P3 tiers?
We have several legacy databases in the Auzre cloud that are in older pricing tiers ("Business" - before Basic / Standard / Premium tiers were available).
We plan to move to new structures (and take advantage of new features) - and as a result, several tests came to an understanding of how much performance gain we will get for pricing the structure.
Azure SQL version is measured in terms of database throughput units (DTUs), and Azure ties these devices to performance improvements: DTU represents the power of the database engine as a mixed measure of CPU, memory, and read / write speed. This measure helps the customer evaluate the relative power of the six SQL database performance levels available in 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 the S0 and S2 database, we stepped onto the P3 version to test it as a benchmark against S0. We were surprised to find that there is no performance difference between running a fairly complex query against the S0 and P3 Azure SQL databases.
Both instances were created from a backup file from a business-level Azure SQL source file (could this be a problem?)
I'm at a loss as I expected the P3 database (800 DTUs) to blow out the S0 database (10 DTUs). Both performed almost the same way. They both used the same execution plan and both had indexes (the DBA installed them, not itself).
Any ideas as to why we didn't see a difference in these configurations?
Does anyone else have examples of performance or performance improvements at these levels?
source to share
Is it possible that your query is running within the resources provided by the S0 database itself, and therefore the higher DB levels no longer help you? The best way to monitor this is to measure your database's percentage of DTU usage when executing a query. If your DTU usage is <100% for S0, you will no longer be able to benefit from the higher DB release levels for this query.
Here's a blog that provides more information on monitoring resource usage and correctly determining the performance level of your database. http://azure.microsoft.com/blog/2014/09/11/azure-sql-database-introduces-new-near-real-time-performance-metrics/
- Srini
source to share
I ran a series of performance tests with Azure SQL Database.
In July 2014, I ran a fairly extensive series of tests. See the results here: https://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-summary/
My most recent tests included comparing the public release v11 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 for why you didn't notice the performance difference between the different service levels, I'm not sure. There are very large differences between the resources available at each service level and performance level. In all my tests, the Premium Tier offers performance much better than the Standard Tier. What test did you run (i.e. what is SQL, and what was the table structure, data volume, etc.) and what were your results (e.g. time)?
source to share