GA Performance in New Azure SQL Database Performance Tiers

The new Azure SQL Database Service Tiers have now reached General Availability.

Performance Metrics

Update (31st January 2015):  For current Performance Metrics, please the current performance test results here (for both v11 and v12).

Changes at General Availability

I will also call here that the pricing for Standard Edition at GA has been reduced compared to the prices stated during the preview.  This is a good step and actually makes the move into the new Service Tiers attractive for lightly loaded databases for cost reasons alone (even ignoring all the other great new features in the new Service Tiers) – more thoughts on this below.

Changing Your Thinking When Approaching the New Service Tiers

The new SQL Database service tiers require that we, as customers, adopt a completely different mind-set in how we approach SQL Database. The higher performance that we want, the more we have to pay. And that performance scale, at the bottom end of the new service tiers, starts very small compared to Web/Business. Standard Tier, even S2, offers on average, significantly less resources than Web/Business.

At the top end of this scale, the resources available reach significantly higher than Web/Business – P2 offering on average more resources than Web/Business.  P1 is a mixed comparison  – sometimes roughly equal to Web/Business, sometimes less than Web/Business, depending on the specific performance metric and how exact you want to be.  P3 (not tested in my performance tests) presumably provides resources way above Web/Business.

But, having said the above, remember this: Your database may perform well on Standard Tier, it may not.  It all depends on how much performance you need.

To be more specific, it depends on how many resources you need to operate your workload.  Higher service tiers offer more IO, CPU, and memory.  I talk “performance” in this post to be more conversational, in the same way people talk about say a “high performance car” as opposed to a “well resourced car”!  But really, performance is “resources divided by workload”, so, for a given workload, higher performance needs more resources.  In Azure SQL Database terms, this means small workloads can obtain good performance on the lower tiers.  Larger workloads need the higher service tiers.  The rest of this post will hopefully help make this picture a little clearer, talking a bit about performance, resources (i.e. service tiers) vs. workloads and how Web/Business compares to the new service tiers.

Consistency of Performance

In all of the talk about the great new features in areas such as geo-replication, self service restore, etc it is easy to lose sight of probably the biggest improvement in SQL Database in the new Service Tiers – consistent performance. This was/is the biggest driver for the creation of the new Service Tiers to replace Web/Business.

The both long-term and short-term random performance dips that SQL Database has struggled with due to noisy database neighbours on the same server should be a thing of the past. The tests I conducted showed that the new tiers do offer more consistent levels of performance over the course of a short test – much less volatile than Web/Business.  It’s “just” that now, to access those higher levels of performance means paying more cash.

As an example that really makes this clear, the following two charts show the Bulk Load Performance for Web/Business vs. S2 to P2.

The “xT” figures refer to number of threads (concurrent database connections) running in each test, e.g. “Business 4T” means a test running with four threads against a database in Business Edition of SQL Database  Each test was run three times (UT1, UT2 and UT3).

The chart shows Web/Business performance is very volatile – on average around 80 to 100 MB/min.  At the extreme, at minute 23, the “Business 4T – UT3” test (grey line) is writing at 140 MB/min, at minute 28 this same test ground to a complete halt at zero MB/min.

The new service tiers by comparison are generally speaking much more consistent.

The “xT” figures refer to number of threads (concurrent database connections) running in each test, e.g. “Prem P2 4T” means a test running with four threads against a database in the Premium P2 service tier.  Each test was run three times (UT1, UT2 and UT3).

Web/Business was writing quite wildly at a rough average of 80 to 100 MB/min.  S2 by comparison is writing consistently at 40 MB/min, P1 at 50 MB/min and P2 at 90 MB/min.  So the average rate of Web/Business is higher than S2 and P1 (for this test) but the rate was much more volatile.  For more details see the Bulk Load Test post.

Which Service Tier is Right for My Database?

My view, very roughly speaking, is that for a databases in the tens of GB (i.e. 10, 20 GB and above), if you were using the full performance available in Web/Business, to reach on average the same performance level as Web/Business will require P1 or P2. Smaller databases may be able to reach and even exceed Web/Business on Standard Tier in some cases.

As ever, it will depend on your specific workload.  Microsoft have released a new DMV in Azure to allow you to see how often a database is hitting the resource limits of the tier that it is hosted in.  See the following blog post for more information:

http://azure.microsoft.com/blog/2014/09/11/azure-sql-database-introduces-new-near-real-time-performance-metrics/

Also, right now, today, you can take a look at the resource utilization figure in your Azure Dashboard.  This is available for Web/Business databases too.  Note, I believe this is only visible in the Preview Dashboard, not the full azure portal.  (To reach the new portal, when logged into the full azure portal, select “Switch To New Portal” from the drop down menu available when clicking on your login email address at the top right of the screen).

This figure is provided as a percentage of the S2 tier capabilities.  E.g. 16% = resource usage at 16% of the maximum resources available if the database was hosted in the S2 Service Tier.  These values can be above 100% (a big improvement over the values in the preview that were limited to a maximum of 100).  This is especially useful, e.g. 200% means resource usage at twice the maximum resources available in S2, so start considering a P1!

Only a single figure is provided in the dashboard.  Look in the sys.resource_stats DMV in the master database in your existing Web/Business SQL Server for more details.  The avg_cpu_percent, avg_physical_data_read_percent, avg_log_write_percent, etc columns here provide more granular usage data.

In advance of you performing analysis, speaking very roughly, the disk IO throttling (and especially the read throttling) on the new service tiers will often be the most significant impact on performance. For databases with only a small “hotspot”, SQL Server caching will likely help you avoid the worst of the read throttling and performance should still be good, even on Standard Tier, e.g. your database is 200GB, but only 500MB of that data is being read (with the rest purely historical and never accessed).

However, I would be very cautious when considering running a database over 10GB, 20GB, etc. in Standard Tier. If your database users step outside of the “hotspot” referred to above, then performance may dramatically drop (e.g. if queries perform scans of large tables). Also, database maintenance operations (e.g. rebuilding indexes, importing/exporting a database) can be very painful on the Standard Tier since they too are subject to the same throttling limits. Of course, you do have the option of switching up to the Premium Tier for these operations.

Equally, if your database workload is very write-heavy, CPU heavy, or memory-intensive then you probably will hit these limits before the read-throttling has any effect.  Profiling your workload and making use of the DMVs described above is the way to find out.

The following example illustrates how bad performance can be on the wrong tier, and how observed performance doesn’t really scale with DTUs. The chart shows how the results of running a mixed workload at scale for two hours. This workload consisted of a mixture of single row inserts, selects and deletes, plus some relatively small multi-row selects – against a 36GB database. The “where” clause in all queries was against the clustered index primary key.

The “xT” figures refer to number of threads (concurrent database connections) running in each test, e.g. “Prem P1 4T” means a test running with four threads against a database in the Premium P1 service tier.  Each test was run three times.

Note these results are plotted on a logarithmic scale. Standard Tier performed very badly for this workload, with less than 1% of the throughput of Web/Business and P1. Clearly P1 is the minimum tier for this workload. Note that the Basic database in this test was 2GB (the maximum size limit of that tier). This explains the much better results for Basic compared to Standard. These tests can be read about in more detail on the Scale Tests post.

So, in summary, databases that were running near the performance limits of Web/Business will need Premium P1 or above. And that does equate to a significant price increase.

Microsoft have stressed to me that, from the usage data they have, they believe very few customers will be impacted negatively by the new Service Tiers. I do however struggle to reconcile that view with my test results but, if many customers were not using the full IO available in Web/Business, then that may well be true.

Microsoft’s DTU Benchmark for the New Service Tiers

I found it interesting that Microsoft ran their DTU benchmarking for the new tiers against what are relatively speaking very small databases. E.g. for S2, their benchmark database was only 7GB – very much less than the total 250GB capacity of S2. I interpret this as another reason to be very cautious about running larger databases in Standard Tier.

Roughly speaking, running a much larger database (say 35 GB = 5 times bigger than the benchmark size), where the same percentage of the database is accessed as in the benchmark, likely means a very much lower overall transaction rate than in the benchmark.

Performance Tuning Is Now More Important

Charging by performance is all arguably the way that Azure SQL Database should have been designed all along. Behind the scenes it is logical to assume the overall higher prices mean the SQL Servers in Azure are not so overcommitted under the new Service Tiers. Bluntly, paying more for Premium means less noisy neighbours and more guaranteed server resources allocated to your database.

Unfortunately, this change of approach (from Web/Business to the new service tiers) by Microsoft means a (possibly painful) adjustment for customers. We may be able to mitigate some of that by performance tuning our databases before the move to the new Service Tiers. I suspect Web/Business has been covering up performance issues in some (many?) databases. By the platform throwing more IO at these databases they have been able to run quite happily. When Web/Business is finally withdrawn, if these issues are still present, we will now need to pay more to cover them. Investing time/money in performance tuning suddenly becomes more cost-effective.

With that in mind, it good news that Microsoft have extended Web/Business by another six months from the originally announced end date of April next year. It gives everyone more breathing room for this adjustment.

Read and Write Rates

The table below summaries the maximum read and write rates from my performance tests.

Edition / Service Tier	My Performance Test Results (July 2014)
	DTUs	Max Read Rate MB/min	Max Write Rate MB/min
Web / Business	N/A	550	100
Basic	5	40	9
Standard S0	–	–	–
Standard S1	15	30	19
Standard S2	50	140	38
Prem P1	100	400	47
Prem P2	200	770	90

Note that these read and write rates are total database throughput.  They are not per connection rates.

Cost – Performance Comparison

The table below is an updated version of the table that appeared in my earlier post.

DB Size (GB)	Cost Now	Usage Level, Future Cost and % of Web/Business Performance
	Web/Business	Very Light Usage (Std S1)	Light Usage (Std S2)	Moderate Usage (Prem P1)	Heavy Usage (Prem P2)
5	$ 26 per month	$ 30 per month for 8% read perf 25% write perf	$ 75 per month for 25% read perf 40% write perf	$ 465 per month for 70% read perf 50% write perf	$ 930 per month for 140% read perf 95% write perf
25	$ 76 per month
50	$ 126 per month
100	$ 176 per month
150	$ 226 per month

For customers with large databases who are not using the full performance capabilities of Web/Business, the Standard Edition pricing is now quite attractive.  E.g. for a customer with a 100 GB database that never uses more than (approx) one third of the performance capabilities of Web/Business, price per month has reduced from $176 under Web/Business to $75 per month under S2.

The new service tiers still represent a significant price increase however for customers who do make heavier use of Web/Business.  E.g. even for a 150 GB database, the price has doubled from $226 (Web/Business) to $465 per month (P1).  For smaller (but still heavily loaded) databases, the price comparison is even worse.  For these customers, the new features in Azure SQL Database (such as geo-replication, self service restore, etc) will have to provide the justification for the move, since cost alone will clearly not.  I rather suspect however, that many of these customers may be forced into looking into alternative options (nb: if you are in this group, please feedback to Microsoft!).

It is interesting to note the very large relative price jump from S2 to P1.  Microsoft have hinted that they were looking at this, considering if customer requirements warranted anything further in this area.

Customer Reactions

I do know that some customers are very upset about the price increases they are facing.  It is human nature to some extent: customers have a view that I pay X to get ABC now, and on the new tiers I now need to pay Y to get ABC.  Where X is much less than Y, that has a high risk of causing a negative reaction, particularly so where that difference is significant in terms of overall spend – which I suspect is more likely to be the case for smaller customers.  For larger customers it may not be significant in terms of total spend or the price increase is still acceptable when considered against the costs associated with on-premise solutions.

To reiterate, my view is that this is the way the service should have been designed all along and paying by database size was less than ideal.  I don’t like the price increases (I am affected too) and believe the S2 to P1 price-performance options still need adjustment.  But, the move to performance based pricing was probably inevitable.

In hindsight, Microsoft would probably admit the Web/Business pricing was a decision made by a rush to get something into the marketplace – measuring and controlling database size is something SQL Server has done for years, it’s quick and easy.  Controlling and segregating performance is more complex and the controls available in SQL Server have only partially existed until now.

Of course, that is little consolation to those customers now facing painful decisions as they make the adjustment to the new reality.

Final Comment

There is a lot to like about the new service tiers in Azure SQL Database.  The greatly improved feature set and more robust performance.  Cost will be an issue for some customers but Microsoft have stressed (for example in the comments on my previous post) that they are listening and have no intention to strand customers.  In my conversations I have found them to be eager to listen and get feedback.

With all of the above in mind I would encourage you to test the new tiers (ideally with a test database or non-critical application in the first instance) as soon as possible and work from there.