Azure SQL Database: V12 Preview Performance Tests – Significant Performance Increase

Introduction

Update 31st Jan 2015:  For updated performance information describing v12 at General Availability – please see the newer post here.

Last week Microsoft announced the new V12 Preview of Azure SQL Database.  This introduces a new S3 performance level and brings to Azure a considerable number of on-premise features that have been missing to date.  These reasons alone make it a very significant update.

I have now performed some quick performance tests on V12 which have demonstrated some very substantial performance improvements.  I have also repeated some of my earlier tests against the current generally available V11 version of SQL Database to provide a direct V11 to V12 comparison.  I will summarise the tests and results below.

Important:  These results are very likely only valid today, for the particular test cases described here.  Given V12 is in preview Microsoft will almost certainly be adjusting performance levels until General Availability.  Please bear this in mind and remember any numbers here describe preview performance at the time of writing only.

Microsoft publish very few absolute performance metrics regarding Azure SQL Database.  The tests described here involve indirectly calculating absolute metrics.  Methodology is described below.

For reference, these tests were conducted against the following Azure SQL Database version (select @@version):

  • Microsoft SQL Azure (RTM) – 12.0.2000.8   Dec  8 2014 05:06:51

Read Rate Test

In my earlier posts I have described on several occasions how the read rate limits inherent in the newer service tiers (as opposed to Web/Business) will, for many databases, be a significant limiter on overall performance.  With that in mind, I am pleased that V12 appears to show very significant increases in read performance.

Test 1 – Sequential Table Scans by a SQL Query from Physical Disk into Memory

This test aimed to measure the maximum rate that data can be read from physical disk into the Buffer Pool.

The test involved using a set of test data that is likely much larger than the SQL Buffer Pool sizes:

  • Basic Edition: 2 GB database
  • Standard Edition:  20 GB database
  • Premium Edition:  30 GB database

A new database was created in each service level and populated to the sizes given above.  The database was then copied (using the Database Copy facility).  My understanding is that this performs a physical copy of the database files (determined from some other tests I have undertaken previously) and results in a new database which has an empty buffer pool i.e. no data cached in memory.  The test then involves sequentially reading through the database reading data from disk allowing the maximum physical disk read rate to be measured.

Data was divided into 1 GB tables and each table was read sequentially.

The SQL statements used to read through the database were similar to the following:

select count(*) from Table0
where [Bit1] is not null and
[TinyInt1] between 10 and 240 and
[Int3] between 100000 and 2000000000 and
[Money2] between 0 and 10000000

No indexes (other than a clustered index on the RowId primary key) were present on the tables.  A count(*) was used to ensure performance is not held back either by complex processing or by waiting for slow network I/O returning results to the client.

Results

rr1

The blue bars show performance values obtained in my earlier tests in July during the preview of the newer service tiers (Basic, Standard and Premium).  The orange bars show performance in the current generally available version (i.e. V11) of SQL Database.  V12 preview performance is shown in the green bars.  Premium P1 and P2 were also tested:

rr2

Now you can see why the Premium results needed to be split into a separate chart.

These results show a few interesting things. Firstly, the very substantial read performance increase in Premium edition.  Prem P2 was reading at 14 GB per minute in this test compared to around 800 MB per minute in the July tests.  My first impression on seeing this result was that perhaps data was somehow in the buffer pool prior to the test, thus making the result appear far too high.  However, I retrieved the query statistics from the query plan cache which reported that 30 GB of physical reads had occurred during that test, so the result appears valid.

It is also interesting to note that V11 performance has increased between the time of my July tests (when Basic, Standard and Premium were in Preview) and today.  This is partly not surprising since Microsoft state that performance will be adjusted during previews. I was however partly surprised since the changes between July and December in V11 are significant (i.e. well beyond minor adjustments).  And these changes occurred without any public announcement regarding a change in DTUs in each performance level – i.e. the stated DTUs in each performance level at the time of my July test are the same as the DTUs today (with the exception of a minor adjustment in Standard Tier to accommodate the introduction of S0).

Overall the V12 preview performance represents a big improvement over V11 and a massive improvement in the case of Prem P2.  Remember however that Microsoft may adjust the performance level between now and GA.

Side Note:  Revisiting July Tests

I reviewed my earlier tests to check if any mistake had been made in the earlier calculations though all seems correct.  For example, during my July P1 read test, the sys.resource_stats DMV was reporting 100% disk read utilisation at around 400 MB per minute.  This firmly suggests Microsoft have made some adjustments in V11 at some point between July and today (probably at GA in September), without any announced DTU change.

Test 2 – Sequential Read Rate Into an Azure Worker Role

The tests described above are a little different to the earlier tests I conducted in July.  My earlier tests measured the read rate into an Azure Worker Role i.e. into a separate machine as opposed to just reading into SQL Server Memory (as described above).

So I also repeated the earlier Sequential Select tests for comparison purposes.  These tests also demonstrated a very significant increase in performance, e.g. a P2 database could be read at 2.2 GB per minute using 8 reading threads in an A4 sized Worker Role, and a P1 database could be read at 1.5 GB per minute using 4 reading threads in an A3 sized Worker Role.

I don’t believe these tests were able to push the maximum read rate of the V12 preview.  During these tests the sys.dm_db_resource_stats was not reporting anything close to 100% in the avg_data_io_percent column (unlike in the July tests, where close to 100% was reported throughout).  This may be due to hitting a network I/O limit between the SQL Server and the Worker Role in the Azure data centre or possibly a network I/O restriction on the SQL Server.  (CPU in the worker role was not an issue – this was monitored during the tests).

Nonetheless, these tests also demonstrate a very significant performance improvement.

Read-rate metrics were captured minute-by-minute through these tests and displayed the same level of consistency as seen in the earlier tests – i.e. that the newer service tiers are much more consistent than the legacy Web/Business editions.

Comparison To Web/Business Edition

For quick comparison purposes, I have included one of the Web/Business Edition test results from my earlier tests in the charts above in this post.  It should be made clear that the Web/Business rate is for a single thread/connection – i.e. a single thread/connection could read, on average, at 550 MB per minute from Web/Business Edition in my earlier tests.  Web/Business performance was typically quite volatile throughout the earlier tests (for example see the chart here), but this was the average value reading from a Web/Business edition database into an Azure Worker role.

On the other hand, the rates for the newer Service Tiers are total limits per database.

This means when opening multiple connections to Web/Business it is sometimes possible to obtain rates much higher than 550 MB / minute.  Therefore a direct comparison is difficult, and hence this note, which hopefully makes understanding the comparison a little easier.

Reviewing the V12 Preview results I believe Microsoft are now moving towards pitching S2 and S3 as viable replacements for some of the smaller workloads in Web/Business.

I have not performed any new tests on Web/Business at this time (not enough time in the day!).

Write Rate Test

Test 1 – Large Inserts by a SQL Query from Memory

This test aimed to cache a relatively small amount of data in the buffer pool (100 MB), then repeatedly write this until a specified total data volume had been written.  Each 100 MB chunk was written using a single Insert statement.  The total data volume was specified to aim for a test very roughly around 1 hour in length  The time taken would then allow an estimate of the maximum write rate.  Total data volumes used were:

  • Basic and S0 – 1 GB.
  • S1, S2 and S3 – 2 GB.
  • P1 – 5 GB.
  • P2 – 10 GB.

Results

wr2

These tests also demonstrated significant write rate improvements compared to V11.

It is interesting to see that the standard tier performance levels appear to have slightly odd write rates in the V12 preview.  S2 actually slightly outperformed S3.  S0 and S1 were broadly similar.  I re-ran these tests twice and obtained similar results.  This is surely evidence that performance levels are still being adjusted by Microsoft and are likely to change.

For the write rates, the July 2014 results are close to the V11 December 2014 results.

Test 2 –  Bulk Load from an Azure Worker Role

Again, a second set of tests was conducted to mirror the July Bulk Load tests.  This involved using the SQL Server Bulk Load interface (via the C# SqlBulkCopy class) to write into SQL Server using multiple threads from an Azure Worker Role.

These results were comparable to those shown above.  The sys.dm_db_resource_stats showed that these tests were able to push V12 to 100% of the log rate limit.  Since even the highest I/O rate was only 250 MB per minute, there was no external limiting factor such as network bandwidth constraining the write rate from the Worker Role, so allowing it to roughly match Test 1 above.

Write-rate metrics were also captured minute-by-minute through these tests and again displayed the same level of consistency as seen in the July tests – i.e. that the newer service tiers are much more consistent than the legacy Web/Business editions.

Memory Limits

The maximum memory usage under each of the performance levels can be estimated using the method described in my earlier post.  This method was used again to try and obtain estimates for V12.

The sys.dm_db_resource_stats DMV was not reporting memory usage information for the S3, P1 and P2 performance levels.  Thus the maximum memory limit could only be estimated for the Basic, S0, S1 and S2 performance levels:

  • Basic – 256 MB = no change from current V11 value
  • Std S0 – 512 MB = no change from current V11 value
  • Std S1 – 1024 MB = compared to 512 MB currently in V11
  • Std S2 – 2560 MB = compared to 512 MB currently in V11

Clearly, these are steps in the right direction and may make S1 and in particular S2 perform much better for some workloads than has previously been the case under V11.

Conclusion

This post has been a very quick run through some performance tests against the new V12 Preview.  Pleasingly, I/O performance, especially read-performance, seems significantly better in V12.

It will be interesting to see where the S3 I/O performance level settles at GA, since S3 may well be the cost-effective option for many existing Web/Business users for whom Premium P1 is simply too expensive (to the point of otherwise potentially leaving Azure altogether for these workloads).  That Microsoft have already said S3 will run at 100 DTUs is a good sign that it will land in this area.

The Premium Service Tier has, in the past, been viewed as very expensive for the level of performance that it delivers.  If the performance at V12 GA lands as it is currently running in Preview, then Microsoft will have made a clear step forward in addressing that criticism.

Advertisements

17 comments

  1. Thank you for this tests. I hope S2 has indeed reached the performance that Web/Business tiers had in the past.
    Why didn’t you include new figures for Web/Business tiers though? Our monitoring software shows significant performance degradation in Business tier since Dec 11th.

  2. Very interesting and informative article. Thank you Chris!
    I’d be curious to hear more from other users on the possible drop of performance of the Web/Business edition after December 11th…

    Nevertheless, Microsoft has definitely made a big step in the right direction in addressing the value proposition of the new service tiers.

  3. I read this article with great interest. I’d previously been unable to find much about the performance of Web/Business vs. Standard. This is exactly the sort of analysis I would have like to have been made available from Microsoft and I’m delighted you stepped in the fill the gap.

  4. More good work as always Chris. I’ve been really impressed by how Microsoft have been very receptive to feedback and are adapting their product suit continually to meet customers needs. I suspect we will be one of the first to jump into S3 when it comes out of preview.

  5. I do not trust the copy database methodology. Maybe the reads were physical from SQL Azures perspective but cached at the storage layer. I recommend that you test by clearing the buffer pool (simply scan a 10GB table multiple times).

    • I do not believe caching at the storage level is playing any significant role for a few reasons:
      1) The databases used in each test were typically copied several hours before the tests were run (i.e. I prep all the tests by copying the databases which takes quite some time, then run them the tests, by re-reading the database copies, 1-by-1, typically 20 GB or 30 GB per database – i.e. not a trivially small data volume). In a multi-tenant environment like Azure, this time gap (at least several hours between database creation and test run) likely means any storage cache has been cleared through the activity of the other databases and other users databases.
      2) As part of other work I conducted a couple of longer tests with much larger databases that were entirely consistent with these results (I haven’t documented these here as I only generally document tests I that have run three times completely independently).
      3) It is my understanding that the throttling in Azure SQL Database is applied by SQL Server, so even if a significant amount of data was cached at the storage level, the key throttling (according to service tier/performance level) will still be applied.
      4) I do know some of the Azure SQL DB team at Microsoft have seen these results – I have a couple of brief conversions over the results on this blog with them and they have been open and frank conversations (which I am grateful for). Whilst all of these results are unofficial and in no way endorsed by Microsoft, in the course of those exchanges, they have not suggested the results are wide of the mark.
      5) Finally, the results have (especially until the v12 preview) not shown the new service tiers particularly favourably from a performance point of view. In previous posts I have highlighted what was relatively quite poor performance between Business Edition and the newer service tiers. Of course, this doesn’t have any direct relationship with the validity of the test results, other than to say generally the results have been lower than might be expected, not higher.

      • I kind of buy the argument you are making.

        I made my comments because 14000MB/min seemed a lot for a P2. That’s 233MB/s which is a multiple of what an Azure blob would deliver. So far Microsoft has always throttled resources very aggressively and often clearly more than technically required. It seems odd that they are now apparently raiding blogs or using local raided disks or SSDs.

        Also, the read P2/P1 ratio is really high. Far more than 2x.

        And then, why is write P2 like 8x slower than read P2? I know of no storage that has such properties. Must be very asymmetric throttling.

        It just all does not really fit together into a convincing picture.

        (Thanks for making all these highly interesting benchmarks!)

  6. Re: anon Jan 29th 1:56pm (WordPress will not allow a direct reply to the comment)

    I agree the S3-P1-P2 ratio looks a little odd in these results (S3 write-performance looks particularly odd). Bear in mind that these results were taken over a month ago, relatively early in the preview (during which time various tweaks are likely to have been made). As v12 approaches General Availability I plan on re-running the tests to see where performance settles to.

    To add to your note about the relatively large P2 performance, I would say a couple of things:
    1) Running P2 is not cheap – i.e. over 500 GBP per month, so I would expect a meaningful amount of resources for that cost.
    2) Microsoft have quite heavily stressed in the v12 announcements that P2 and P3 have received significant performance improvements, e.g. see the section entitled “Performance improvements at the Premium service tier” towards the bottom of this page, which is one of the original announcements/info pages:
    http://azure.microsoft.com/en-gb/documentation/articles/sql-database-preview-whats-new/
    No mention is made on this page of P1, which implies either P1 will not be receiving such a significant boost yet, or perhaps Microsoft just had not yet fixed/increased the P1 performance at the time that announcement was made (which was also around the time my tests were run).
    3) In one of my exchanges with Microsoft, it was mentioned that there has been a quite significant improvement in the hardware behind the scenes that is being used to run the Azure SQL DB service. The tone of the conversation suggested this hardware upgrade had been long planned/awaited and they are finally now being able to deploy it into the data centres. As such, it makes sense that we are seeing quite significant performance improvements. It also means the pricing of the Premium tier that was announced last year starts to make sense. Last summer, compared to Web/Business edition, I (and am sure others) thought the price of Premium was extremely high, i.e. multiples, almost orders of magnitude more expensive than Business, without the same corresponding performance increase. Now, however, as the performance improvements are being delivered (and a very noticeable gap is opening up between Business and Premium), that pricing is starting to make more sense. I expect Microsoft always had these performance levels in mind when the Premium Tier prices were announced.

    As for the write vs. read asymmetry, I would only say that write performance is perhaps/probably more difficult to increase than read performance. Because of the High Availability / Disaster Recovery mechanisms running, there are probably a lot more moving parts making increasing that more complex / costly.

    • My thinking is that hardware cost is probably not significant on SQL Azure. The prices are a multiple of what you’d pay for storage and compute (including the extremely high margins on compute). My feeling is that the throttling is so severe that an equal-cost compute+storage would totally kill SQL Azure on performance. Like an order of magnitude. (Maybe this would be an interesting benchmark target – I’d be highly interested in work comparing equal-price performance or equal-performance price.)

      So I doubt they are pricing on cost (which is actually always wrong – you always price on customer value). That’s why compute has 80-90% margins.

      There is no technical reason for IO throttling. They could just RAID together 16 page blobs internally and offer 1280MB/s = 76GB/min of sequential throughput. In fact they could RAID even more – the 16 disk maximum is artificial. Assuming a customer would pay for it they could RAID 50 SSDs. That would be quite an offer.

      That’s why I think hardware upgrades cannot be the reason for any changes. Offered throughput is purely a policy choice. It’s a constant in the source code of Azure.

      Azure seems to be based on the mindset to always limit all feature artificially without any technical need. I don’t understand why they are doing this. They could at least offer it and make customers pay through the nose.

  7. Pingback: Azure SQL Database V12 管理實戰 - TechNet Taiwan 官方部落格 - Site Home - TechNet Blogs

  8. Pingback: Azure SQL Database V12 管理實戰 - Microsoft Azure 中文部落格 - Site Home - TechNet Blogs


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s