Azure SQL Data Warehouse, Power BI and old problems

Azure SQL Data Warehouse is a fantastic MPP solution that puts an incredible amount of data storage and processing power at your fingertips.  With all of the benefits of the cloud and great SQL Server compatibility as well.

Power BI has native connectivity to  Azure SQL DW, including via Direct Query, allowing interactive reports to be run over extremely large data volumes.

One niggle is the inherent limitation in Power BI of only being able to create one relationship between each table.  For one of my recent use cases with Azure SQL DW, this Power BI limitation meant it wasn’t possible to have Power BI generate queries that are optimum.

Generally speaking with Azure SQL DW, eliminating/minimising the data movement between the compute nodes in query plans is a good thing.  In my scenario, I could easily hand write a SQL query that minimised data movement by including an extra join condition.  Power BI can’t generate this extra condition however, so that plan isn’t so optimised.

All the details are here:

It’s a modern example of the age-old problem that handwritten code can usually be written that outperforms machine written code.  We have all shiny new technology, and incredible technology it certainly is, but often the age-old niggles are still there…

UPDATE  14th May 2018:  The Power BI team has added the COMBINEVALUES() function, which helps greatly with the above problem.  Details already blogged at:

Azure Increases SQL Database Performance

Microsoft have recently increased the performance across all service tiers at no additional cost:

  • Write performance is doubled for all service tiers
  • Read performance is doubled for the Premium service tiers.

The official announcement is here.

For the benefit of anyone reading the older posts below, please remember some of the numbers should (in theory) be doubled.

Azure SQL DB: Quick Retests of Standard Tier

For a current piece of work, I needed updated information about Standard Tier capabilities in Azure SQL Database, especially raw write capability.  In my main v12 testing in Jan 2015, Microsoft were still adjusting v12 write performance for the Standard Tier.

Therefore, I have done a few quick retests.  Results:

  • S0 write:  24 MB per minute = 0.4 MB per second.
  • S1 write:  36 MB per minute = 0.6 MB per second.
  • S2 write:  48 MB per minute = 0.8 MB per second.

This shows Microsoft have adjusted the performance of S0 to S2 downwards from the performance in Jan 2015.  This is not surprising, since the results in Jan 2015 showed S0 and S1 had roughly equal performance, with S2 significantly more (even higher than S3).

I also ran the memory tests again.  The results reasonably closely matched my earlier tests (i.e. S0 = 500 MB, S1 = 990 MB, S2 = 2.2 GB).

It is worth noting that some blog posts have appeared over the past few months (e.g. here) that talk about using dm_os_performance_counters to obtain memory information.  For standard tier at least, I don’t believe these numbers are what they appear to be.  For example, target server memory using this DMV for both S1 and S2 is 3GB.  The dm_db_resource_stats DMV numbers suggest it is impossible to utilise 3 GB of memory on either an S1 or S2.

(All methodologies in these retests were the same as in the earlier tests).

Azure SQL Database: v12 GA Performance inc. CPU Benchmaring


Version 12 of Azure SQL Database has now reached General Availability in Europe.  See the announcement here.  So, now it is time to re-run those earlier performance tests, to see where performance has settled at.

This post includes:

  • updated read / write metrics
  • a new CPU benchmark and
  • updated memory allocation metrics.

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

All tests were conducted at least three times to check for consistency and to reduce the risk of any random variations affecting the results.  (That is a lot of tests, data collection and processing – nearly 80 test runs in total).

For reference, these tests were conducted in the Western Europe data centre, against the following Azure SQL Database version (select @@version):

  • Microsoft SQL Azure (RTM) – 12.0.2000.8   Jan 29 2015 07:51:58

Unit of Measure

Note that the unit of measure for throughput rates in this post (and the other posts in this series) are MB per minute.  This is primarily because, at the lower tiers (and even the higher tiers until recently), rates of MB per second would be 0.xx, 1.xx, etc.  As the throughput rates have been increased, particularly recently, this has led to the higher performance level rates entering the thousands, so we have the opposite problem (i.e. too large numbers at the top end).  Still, despite that, I have opted to retain the same unit of measure throughout, to keep comparisons easier.  In the future, I may move to MB per second, if I get a lot of feedback in that direction.

Read Rate Tests

The test is based around reading data from a series of identical tables.  The tables contain a mixture of basic data types – no large object data types, all in-row data.  Average row size is around 410 bytes.

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

  • Basic: 1.9 GB
  • Standard S0:  10 GB
  • Standard S1:  20 GB
  • Standard S2:  30 GB
  • Standard S3:  36 GB
  • Premium P1 and P2:  36 GB

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 (100 MB tables for Basic Tier) 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.



The blue bars show performance values obtained in my first tests in July 2014 during the preview of the newer service tiers (Basic, Standard and Premium).  The red bars show performance in the current generally available version (i.e. V11) of SQL Database.  V12 preview performance, measured in December 2014, is shown in the green bars.  The orange bars show V12 GA performance (except for Web/Business, which only exists at v11 – the orange Web/Business bar shows the result of a v11 test conducted in January 2015).

Premium P1 and P2 were also tested:


Several interesting observations here.  Most notably, Premium P1 and P2 have received very significant read performance increases.  Premium performance now makes Web/Business performance appear tiny in comparison.  With this performance we can perhaps now understand why Microsoft set the Premium Tier prices so expensively in comparison to Web/Business.  No doubt, last summer when the Premium Tier pricing was announced, it was with this performance level in mind.  I expect Microsoft faced an interesting dilemma last year, do they pitch the initial Premium tier price lower in July 2014 (in line with the initial lower performance) and then dramatically increase it in January 2015 as this v12 upgrade comes on-line (and annoy customers in Jan 2015), or do they set it higher from the start (at the risk of making Premium look very poor value initially).  Obviously they chose the latter, and now the full story is becoming clearer.

This is a good point to note that at the beginning of these tests, the SQL Server Buffer Pool was empty (verified by checking the memory usage column in the sys.dm_db_resource_states DMV).  In addition, five to six hours had also elapsed between the creation of the database copies and the running of the tests, which should reduce potential effects from storage layer caching (though there is no way to verify this).  Thus the tests should be measuring the read rate from disk, not cache.

Standard Tier v12 GA performance for S1, S2 and S3 is significantly less than in preview.  However, it would not be fair to criticise Microsoft for any performance changes here.  It was quite clearly stated during the preview that performance could/would be adjusted as the service heads towards general availability.

More important is the comparison between current v11 performance and v12 GA performance.  Here we can see that maximum read rates under S0 and S1 have more than doubled.  S0 and S2 have received a slight increase.  Curiously Basic tier seems to have received a slight decrease.

It is particularly interesting to see where S2 and S3 read performance now sits.  S2 runs at almost the exact average of Web/Business.  Current Web/Business read performance is on average around 550 MB per minute though is quite volatile flipping between 200 MB per minute and 800 MB per minute (see my previous post here for more details).

S3 runs at on average 900 MB per minute – i.e. well above the average Web/Business performance and above even the Web/Business peak rates.

There is a wide gap between S3 performance and P1 performance.  Both S3 and P1 are rated at 100 DTUs, though P1 costs five times as much as S3.  Clearly, for that price difference you get a massive amount of extra read-performance.  This suggests that the DTU figures do not take into account I/O performance limits/variations between tiers / performance levels.

Read Rate Consistency

The following charts show how the read rates varied over the duration of the tests.  Each read test was performance three times at each performance level – labelled as (a), (b) and (c) in the charts:


A point is plotted on the chart for each table read during the test.  For Basic tier, each table contained 100 MB (showing first  1GB only on the chart).  For the other service tiers, each table contained 1 GB.

The chart shows that read rates were generally very consistent throughout.  Continuing with the other service tiers:


S1 read performance was generally consistent, S2 read performance varied a little but there does seem to be some quite wide variation of S3 read performance.  For P1 and P2:


Again, both P1 and P2 are reasonably consistent in their performance.  The P2a test run was a little more variable than the other two P2 tests.  S3 is shown on this chart to illustrate just how much higher Premium performance is than S3.  On this chart, the scale is such that the relatively wider variation in S3 performance (shown on the previous chart) is barely even visible here.

Read Test Resource Usage

During the read tests, the data from the sys.dm_db_resource_stats DMV was captured.  This showed that, in the S0, S1 and S2 tests, around 90% of the available read IO resources were being consumed, so these results should be a good reflection of the maximum rates available.

The S3 result was a little more volatile, showing that at times significantly less than 100% of the available read resources were being consumed.


As you may expect, the drops in read utilisation are generally speaking at the same points in the test as the drops in read rate occur (e.g. for at 8 GB in S3b and 22 GB in S3c).  This suggests the I/O subsystem was unable to supply the maximum amount of data per second that SQL Server could consume in this performance level.  All in all this suggests that Microsoft still need to apply some performance optimisations for S3.

Interestingly, the chart for P2 also shows that it was not consuming all of the theoretically available read resources:


This chart has less detail than previous charts.  This is because P2 read the test database very quickly, and there is only one row in the sys.dm_db_resource_stats DMV for every fifteen seconds.  Even still, the general trend is still clear enough.  The test query was consuming only around 70% of the available read resources during the test.

Knowing that the P2 test read on average at 19,500 MB per minute at 70% read utilisation, we can estimate that the theoretical P2 maximum read rate would be around 28 GB per minute.  Similarly, we can estimate that the maximum theoretical read rate for P1 (which was reading 11,800 MB per minute on average at 80% read utilisation) would be around 15 GB per minute.

More Read Utilisation charts for the other performance levels can be found in an appendix at the end of this article.

Write Rate Tests

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 30 minutes to 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.



Both Basic Tier and all four performance levels within Standard Tier have received notable write rate increases.

However the first question here is this: why is S3 write performance notably less than S2 write performance?  Tests at both S2 and S3 were repeated three times each – the results were consistent in all cases.  This seems very strange and I have put this question to Microsoft.

Edit:  I have received a response from Microsoft that the current write rates for the Standard Tiers are in v12 are about to be adjusted.  Once this has been completed I will update the details here.

It is interesting to note however, that none of the performance levels in Standard Tier come close to matching the Web/Business write performance.  Having said that, if S3 write performance was actually above S2 (as surely it is supposed to be) than S3 may actually come close to Web/Business.  I can see this being a key question for many existing Web/Business customers.

Moving on to write performance of the Premium Tier:


P1 and P2 see significant write performance increases – a factor of almost three over current v11.  P1 write performance is now clearly higher than Web/Business – which is not the case in v11.  v12 P1 writes also outperform v11 P2.

Write Rate Consistency

The following charts show how the write rates varied over the duration of the tests.  Each write test was performance three times at each performance level – labelled as (a), (b) and (c) in the following charts.

This data shows Basic, S0 and S1 writing very consistently at around 45 MB per minute throughout.  These charts are included in an appendix at the end of this post (since they all write at around 45 MB per minute, each performance level must be plotted on separate charts to avoid all the lines overlapping – which results in just too many charts to include in the main body of the text).

Comparing S1, S2, S3 and P1:


This chart shows how the write rates for S1, S2, S3 and P1 are very consistent in all tests.

This chart also shows how very consistently S2 outperforms S3.  Surely there is a bug here in Azure?

Now comparing P1 and P2:


Again, the write rate is extremely consistent for P2.

Write Test Resource Usage

During the write tests, the data from the sys.dm_db_resource_stats DMV was captured.  Unsurprisingly given the consistency shown above, this data shows the write-utilisation at very close to 100% (i.e. 98%, 99%), throughout.  As such, there is very little point plotting the data.

CPU Tests

In previous series of tests I have not compared CPU performance between the different performance levels.  As part of this series of tests, I decided running some tests to compare CPU performance between the different performance levels might be interesting.

The test is based around running the same CPU-heavy workload in each of the different performance levels and comparing the results.  The chosen workload is a forced loop join where the join condition is something non-trivial, for these tests:

(abs(((t1.Int1 * t2.Int2) + t1.Int3) - ((t2.Int1 * t1.Int2) + t2.Int4)) < 17) and (abs((power(t1.Int5 % 5, 3) - (t2.Int5 % 25))) < 7)

This query means that a relatively small number of rows will require a significant amount of time to process.  Using a relatively small number of rows is important because it eliminates any potential delays due to time waiting for the data to be read.  The test table was created with 280,000 rows of data which is in total around 10 MB.  This entire table was read before the test began properly to get the data loaded into the buffer pool.  All in all this means close to all of the query execution time is CPU time.

The query was written so that SQL Server should use a parallelised execution plan.  This allows the workload to expand to exploit the full amount of CPU resources available within the performance level.  The exact same data was used for every test.

The test script used automatically ran the test multiple times, recording the results for each test.  The number of rows selected from the test table was increased with each test (SELECT TOP … ORDER BY RowId was used to ensure data is picked up in the same way in each performance level).  The number of rows was increased until the SELECT ran for over two minutes, at which time no further tests were peformed (i.e. the row count wasn’t increased further).

The test was developed on my development machine.  This has the added advantage that the Azure results can be compared to a machine of known specification.  My development machine was built in 2010 – at the time it was a relatively high end machine.  At the core of the machine is an Intel i7 930 2.8 GHz quad core processor with hyperthreading (i.e. eight logical cores) running on an ASUS P6T Deluxe motherboard.  Details of the processor can be found here.  Details of the motherboard here.  My development machine is running an up to date copy of SQL Server 2014 so it is assumed that the engine internals involved in the test are close enough to those running in Azure SQL Database to make a comparison valid.


Given the relatively large number of test cases, each with different row counts, it is easier to display these results in tabular form:


The table above shows three metrics, explained below.

Average query time in seconds

This shows the time taken to execute the test query in each performance level – row count combination.  For example, in the S0 column we can see that 34 seconds were required to perform the join, where the first 3000 rows were selected from the table.  The Ref column refers to my development machine (i.e. the reference machine).

In case anyone is jumping to the wrong idea… as I said above, the test query used was deliberately chosen to take a relatively long time to execute.  This query time is not indicative of the time taken to join 3000 rows in a typical business scenario!  In fact, the actual execution time is not really meaningful since the query is junk – all we care about is that it is a relatively CPU heavy workload that we can repeatedly execute in different environments and compare the execution times.

It is by comparing results between different environments that more meaningful insights can be gained.

When interpreting this table, it is important to understand what are valid/simple comparisons and what aren’t.  Comparing along the rows is valid e.g. comparing the the time to join 7500 rows on an S0 and an S1.  Joining the first 7500 rows from the test table on an S2 required 49 seconds.  Performing exactly the same join across the same test data on an S3 required 24 seconds.  Since it was the same workload, we can say S3 is twice as powerful in CPU terms as S2.

Comparing between rows in this table is not so simple.  I.e. this workload does not scale linearly.  E.g. on an S2, joining 5000 rows required 22 seconds.  Joining 10000 rows required 86 seconds.  As we are talking about a loop join here, double the rows equals four times the workload, which the results roughly agree with.

Finally, very short query times are unlikely to be good sources of information.  It is possible in much shorter queries that SQL Server may use a different execution plan.  It is also possible a larger percentage of the time may be attributable to non-CPU sources.  This is roughly those results from joining 2000 rows or less, which is why these rows have been grayed out in the table.

Performance Multiple of Reference System (Quad Core 2.8 GHz i7 930)

This metric is shown in the middle part of the table.  If we assign my development machine a metric value of 1, then comparing the query times between this system and the different Azure performance levels, we can see what fraction of my development machine CPU resources those performance levels offer.  For example, if a (CPU-heavy) query runs in ten seconds on my machine and takes twenty seconds in a particular performance level in Azure, we can say that this performance level in Azure offers half the CPU resources of my development machine.

The numbers highlighted in yellow show these results.  These show that, for example, S3 offers about one-fifth of the CPU resources of my dedicated development machine.

DTU Multiple

This metric is shown in the latter part of the table.  Taking S2 at 50 DTUs as a reference point and using the performance multiples above, it is possible to calculate the effective DTUs of each of the other performance levels.  The numbers highlighted in green show these results.


It is interesting just how closely these results align with the number of DTUs allocated per performance level in Azure.  It is clear that the CPU resources allocated to each performance level in Azure SQL Database very closely reflect the DTU values stated by Microsoft.

By this calculation, my desktop machine offers the equivalent (in terms of CPU resources) of 500 DTUs!  I was a little surprised that even a P2 database has less than half of the CPU resources of my development machine.  Though, as I said, my development machine is still moderately high-spec (and probably way overpowered in CPU terms) and of course, my development machine does not offer any of the other great features in Azure SQL DB!

P2 Query Plans

In some of the test results, P2 appears to only offer around 150 DTUs.  These are the cells highlighted in red above.  This was actually due to Azure SQL DB using a slower query plan when executing these test cases.  For all of the other tests, the sys.dm_db_resource_stats DMV showed 100% CPU utilisation when running the tests.  For the test cases highlighted in red, CPU utilisation was reported at being only around 70%, hence the query took longer to execute.  From the 17500 row test case onwards, the faster query plan was used.  The changeover between the two plans can be seen clearly in the CPU Time results.  Normally, in a given performance level, as the row count is increased there is a significant CPU time increase too.  However, while 15000 rows required 64 seconds to execute, 17500 rows required 66 seconds, i.e. only two seconds more, as a result of the switch to the faster query plan.  These are the two timings highlighted in orange in the table above.

Memory Tests

The maximum possible 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 obtain estimates for V12:



These results show some significant changes to the memory allocated to each performance level between v11 and v12.  S1 and S2 are able to access a significantly higher amount of memory in v12.

Interestingly S3 and P1 are allocated the same amount of memory, which mirrors the fact they both have the same DTU rating of 100.  This does mean a significant reduction in the amount of memory available under P1, however this change does make some sense.

Under v11, the memory allocated was solely determined by service tier (i.e. Basic, Standard or Premium).  All databases within a given tier had the same memory allocated, irrespective of the performance level.  Under v12, this has become more fine grained, being based on both service tier and performance level within the tier.  With this in mind, the change to P1 memory allocation can be seen to be more like a correction.  Under v11, P1 was over allocated relative to P2 (or, from the other point of view, P2 was under-allocated compared to P1).  The same applies to S0, S1, S2 and now S3.


Version 12 of Azure SQL Database has brought many significant performance increases over current v11.  The read rate improvements for S1 and S2 are significant.  The read rate improvements for P1 and P2 are dramatic.  The write rate improvements are also significant, especially again for the Premium Tier.  Finally this lifts P1 performance well above that of Web/Business.

The most significant questions raised by these tests are around S3.  S3 write performance is less than S2 – surely a bug/issue.  There also appears to be some volatility around the S3 read rate consistency currently, hopefully this will settle down shortly.

For customers looking to migrate from Web/Business, S3 still looks promising, however the question of the S3 write rate needs answering.  If this is not rectified, then moving from Web/Business to S3 will incur a roughly 50% cut in the write rate available.  If affordable, Premium now offers a level of resources significantly / dramatically above that available in Web/Business.

These tests have shown how the DTUs allocated to each performance level now closely reflect the resources available in each tier.  For the memory and CPU allocation there is close to a direct correlation.

Edit:  2nd Feb 2015:  Additional Test Results

I have run a couple of additional tests (following the same methodology) since the rest of this post was written that have thrown up some interesting results.  I’ll walk through them:

Massive P2 Read Rates

I conducted a further short series of P2 read tests that have shown dramatically different read rates.  I repeated the tests in the other tiers which did not show any significant increase.

  1. Repeat of P2 Read Test:  Average Read Rate = 39,200 MB per minute
  2. Repeat of P2 Read Test:  Average Read Rate = 39,800 MB per minute
    Clearly a massive increase on the earlier tests.  The read rates were sustained throughout the tests.  Several hours elapsed between the database copies being created and the tests being run (as in the earlier tests).  Buffer pool was empty at the start of the tests.  Perhaps the effect of data in a storage cache?
  3. Repeat of P1 Read Test:  Average Read Rate = 12,000 MB per minute
    i.e. matches the earlier tests described in the main body of the post.
  4. Repeat of S2 Read Test:  Average Read Rate = 580 MB per minute
    i.e. matches the earlier tests described in the main body of the post.
  5. Repeat of S0 Read Test:  Average Read Rate =  292 MB per minute
    i.e. matches the earlier tests described in the main body of the post.

Effect of NOLOCK Hint

Using the NOLOCK hint (where practical) can increase the read rate, significantly so for the premium tier:

  1. P2 Read Test:  Average Read Rate = 69,000 MB per minute
  2. P2 Read Test:  Average Read Rate = 60,900 MB per minute
    Almost double the P2 read rate result from earlier in the same day.
  3. P1 Read Test:  Average Read Rate = 13,800 MB per minute
    i.e. around 2,000 MB per minute above the earlier result.
  4. Average Read Rate = 750 MB per minute
    i.e. around a 170 MB per minute increase over the earlier result.
  5. Repeat of S0 Read Test:  Average Read Rate =  340 MB per minute
    i.e. around a 50 MB per minute over the earlier result.

Appendix: Additional Read Test Resource Usage Charts

See text above for explanation.


The Basic and S1 charts were very similar to the S0 chart, i.e. close to 100% read utilisation throughout (sorry, just too many charts to produce to create and include them all!).



Appendix: Additional Write Rate Consistency Charts

See text above for explanation.




Has Azure SQL Database Business Edition Got Slower?


Since Microsoft released the Azure SQL Database v12 Preview in mid-December, some users have suggested there was, around the same time, a noticeable drop in the performance of the legacy Web/Business editions of Azure SQL Database.

In this post I re-run some of my earlier tests to see if that really is true.  I was planning to revisit Business Edition in any case, to get some better and more up-to-date metrics for comparison to V12 when that heads into General Availability.

July 2014 Performance

The Sequential Selects read tests performed in July 2014 estimated that Web/Business Editions read from physical disk on average at 550 MB per minute (peak 650 MB per minute).  See those earlier tests here for details.

Several write tests were also performed in July 2014.  All were reasonably consistent and estimated the maximum write rate at around 90 MB per minute.  See for example the Bulk Load tests here.

Quick Test

Before setting up any elaborate tests, I ran a quick initial read test – basically a sequential read of the whole of a 37 GB database.  This took 2 hours 50 minutes, equating to a read rate of around 225 MB per minute.  Clearly, much less than the July 2014 results and a sufficiently big enough difference to warrant further investigation.

Re-running the Sequential Select Read Tests:  Read Rate into a VM

The first full tests to run were a repeat of the July 2014 Sequential Select Tests.  This should allow a direct comparison between performance in July 2014 and January 2015.

The Sequential Select test involves reading an entire database to get a simple estimate of maximum read rates. The data is read table-by-table, and each table is read sequentially (i.e. roughly speaking SELECT * with no ORDER BY).

The test involves using a newly restored database. The database is 36 GB in size.  The test lasts 60 minutes.

The size of the database and the fact it is newly restored means that none of the data exists in the buffer pool and the test therefore estimates the maximum read rates from physical disk.

The data is read into a Windows Azure Worker Role (i.e. a VM in the same Azure data centre) which measures the data read rate and captures various other metrics.  The test uses a single reader (i.e. a single thread) which is typically sufficient to hit the read rate limit (more details on this below).



The chart shows three different runs of the same Sequential Select test.  The read rate is quite volatile, flipping between around 200 MB per minute and around 500 MB per minute.  The first test ran almost entirely at 500 MB per minute, the second test mostly at 200 MB per minute, the third test flipped between the two rates.

Comparing with the July 2014 test results:


The read rate was much more stable in the July tests at around 550 MB to 600 MB per minute, though not completely stable – the third Business Edition test shows a drop to 200 MB per minute, though it is brief.

This suggests Business Edition read performance has become more volatile since July 2014.

Alternative Read Test:  Maximum Read Rate Test inside SQL Server

The older test, described above, was based on reading from the test database into a Azure Worker Role – i.e. a VM outside SQL Server.

The more recent tests I have conducted against the newer service tiers in v11 and preview v12 (see previous blog posts in December 2014) involved measuring the read rate using a simple aggregate query (i.e. select count(*) from … where … ).  This performs table scans across an entire database but the data does not leave SQL Server (only the final count(*) value is returned by the query).  Compared to the previous test, this test has the advantage that it does not involve any significant network activity between SQL Server and a VM outside of SQL Server which may have constrained the read rate in the earlier test.  This should therefore allow a better estimate of the maximum read rate from disk, free of any network constraints.  For more details of this test, see the “Test 1 – Sequential Table Scans by a SQL Query from Physical Disk into Memory” heading in the previous post here.



Above: The data was read from the database table-by-table.  Each table was 1 GB in size.  The time taken to read each GB was measured.  The chart above shows the read-rate for each GB of data read during the test.  E.g. we can see the 24th GB in test 2 was read at a rate of around 275 MB per minute.  The test was repeated three times.

The flipping between two different read rates is even more clearly shown in this test.  The maximum read rate here is actually 800 MB per minute, higher than the 550 MB per minute seen in the earlier tests.  As suggested above, this higher rate is very likely explained by a network I/O restriction somewhere between SQL Server and the Azure Worker Role VM used in the earlier test.

However, more interesting in these results, is the fact that the flipping between the two different read rates is even clearer here.  This suggests internal logic in Business Edition that, once a given SQL Server is overloaded (i.e. when all of the different databases from all of the different users on that server attempt to read too much data too quickly), then the read rate for some/all databases on that SQL Server is limited to 200 MB per minute until the overload passes.  This is probably a simpler mechanism that provides a more reliable database service than failing databases over to other SQL Servers to attempt to balance workload (causing outages in the process).

Re-running the Scale Tests

The Scale Tests from July 2014 involved executing a mixed workload of inserts, (single and multi-row) selects, updates and deletes against copies of the same 36 GB test database.  This test was re-run as it provides a more complex workload than a simple sequential read – which is clearly not a typical workload.

Again, the test starts with a newly-restored database.  The requests are not uniformly distributed across the database (mirroring most typical OLTP workloads) so that the SQL Server Buffer Pool should have an increasing effect as the test runs and more data is cached in memory.  See the earlier post here for more details on this test.

In July 2014 tests were performed against Business Edition with different numbers of worker threads (i.e. number of concurrent requests).  The test re-run here is the heaviest of those tests – the 8T test from July 2014 – i.e. 8 worker threads running in parallel in the VM, generating requests against the test database.



The lines on the chart show the number of operations (i.e. SQL statements) executed per second.  The blue lines are the July 2014 results.  The test was run three times in July 2014.  The green lines are the January 2015 results – again the test was run three times.

Clearly, performance is comparable between July 2014 and January 2015.  The performance is quite volatile in all six tests (as is to be expected in Business Edition where all users share/compete for server resources).

Averaging the three results from each set of tests:


The latter stages of the test perform better in the more recent tests.  There is no difference in the types of request generated as the test progresses.  The only thing that changes as the test progresses is that the SQL Server Buffer Pool will have more data cached (i.e. the cache starts empty and fills as requests are processed).  Therefore, this difference between July 2014 and January 2015 could be explained by an increase in the size of the SQL Server Buffer Pool per database since July 2014 allowing more data to be cached – or it could simply be a random result given the inherent volatility in Business Edition.

Write Rates

The write test conducted involved using a SQL query to write 100 MB chunks into a database, up to 10 GB in total, and measuring the write time for each chunk.  The query was executed directly on the server – i.e. no external VM and no VM to SQL Server network hops were involved.  Again, the test was executed three times.



Above: The data was written to the database in 100 MB chunks, up to a total of 10 GB.  The time taken to write each 100 MB chunk was measured.  The chart above shows the write-rate for each chunk written during the test.  The test was repeated three times.

Clearly, the write rates are quite volatile (just as they were in July 2014).  Smoothing (via a rolling average):


The average write rate in the first test was around 87 MB per minute.  In the second and third tests the average rate was almost exactly 100 MB per minute on average.  Entirely consistent with the July 2014 results.


The Business Edition of Azure SQL Database has always featured / suffered from variable performance.  These tests appear to show the performance is more volatile in January 2015 compared to July 2014.  However there is no clear evidence for performance having been “turned down”, e.g. no lower maximum performance limit has been imposed.

The ratio of SQL servers to user databases in Azure SQL Database has likely always varied as new user databases and new batches of servers are deployed at different times.  It is probable that last July there was more spare capacity and a so a lower database density (i.e. number of databases per server) across the SQL servers in the Azure data centre.  Now, it is possible that the database density has risen and as a result performance for individual databases is being trimmed back more often to balance workloads (such as the flipping between higher and lower read rates seen in these tests).

Conspiracy theorists may suggest Microsoft have increased database density to reduce performance.  Personally I think that is unlikely.  Business Edition has always been volatile and some users have always had to endure periods of more volatile / lower performance.

Indeed, the entirety of this post shows why Business Edition is difficult to work with and will inevitably lead to difficulties for/between Microsoft and users.  Performance varies and drifts over time and there are no performance benchmarks to plan against, test against, measure against, etc.

The long term solution to this variable performance is to move into one of the newer service tiers where the performance boundaries between different databases are much more rigorously enforced.  Previously this would have incurred a much greater cost compared to the Business Edition cost.  However, the new S3 service tier in the upcoming Azure SQL Database v12 (currently in preview) should help here, providing a closer price/performance fit to Business Edition than has been available to date.  See my earlier post here for more details.

And Finally…

These tests were carried out in the Western Europe Data Centre.  It is possible performance may be different in other data centres (e.g. where the ratio of SQL Servers to user databases may be different).

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


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.



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:


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.



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.


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.

Latest Azure SQL Database Changes: V12 Preview

Update (18th Dec):  For performance test results, including comparing v11 with v12 Preview, please see the following post.

This is a very brief post since I am currently busy with other things and haven’t had time to test the Azure SQL Database V12 Preview yet.  In short, V12 brings a whole heap of missing “on-prem” edition features to Azure and introduces a new performance level, S3.  All great things.  I am however a little bit confused about the story of the S3 performance level.  I’ve posted a comment on Scott Gu’s blog post:

I am happy to read about the improvements to Azure SQL DB. Excellent news that the on-prem/IAAS vs. PAAS feature gap is closing in a big way. I do have a couple of questions though:
We now seem to have both S3 and P1 at 100 DTUs, but with very different prices, which seems odd. Even though P1 offers numerous other/improved features over Standard Tier, the price difference seems very large given the “DTU” equality.
Further, it is stated that P2 and P3 are getting I/O improvements, but there is no mention of changes to the DTUs to these tiers. If actual performance (i.e. IOPs) is changing, then presumably DTUs should be increased (unless the definition of a DTU itself is changing). I am a bit confused. The story here seems incomplete. Can you help clarify things?

It almost seems as though there might (pure speculation) be some other as-yet unannounced changes to the other existing Standard/Premium service tier performance levels coming to straighten this out (perhaps at the V12 GA?).  Maybe, then again maybe not – the whole DTU story has always been a little bit confusing to me!

Hopefully I’ll get some time to take V12 for a test drive over the next couple of weeks.

Azure SQL Database Memory Limits By Service Tier


The new service tiers in Azure SQL Database provide different levels of performance, by offering more of the following at each higher service level:

  • Processing (CPU) Time
  • Read Rate (from physical disk)
  • Write Rate (to the database log file).
  • Memory (space to cache and process data)

One of the frustrating aspects of the new service tiers on a technical level is that Microsoft are not providing any particularly meaningful performance units to measure the new tiers by.  Everywhere you look you find relative units, be it DTUs or be it percentages that measure resource utilisation in each tier or compare between tiers (e.g. in the sys.resource_stats / sys.dm_db_resource_stats DMVs in the master / your database respectively).

My earlier performance tests provided some information about the maximum read and write rates supported by each tier.

I’ve now performed some further tests that provide some information about the memory limits in each tier / performance level.  Results are given first.  The methodology is at the end of this post.

Test Results – Azure SQL DB Memory Allocation

The tests suggest each database is allocated memory as follows:

  • Web / Business Edition – at least 5 GB (likely variable).
  • Basic Tier – 250 MB
  • Standard Tier S0 – 512 MB
  • Standard Tier S1 – 512 MB
  • Standard Tier S2 – 512 MB
  • Premium Tier P1 – 9 GB
  • Premium Tier P2 – 9 GB

Premium P3 was not tested.  It is not possible to obtain exact figures for Web/Business edition since the DMVs don’t contain memory information in that edition.  However, repeating the same tests has proven that it is possible in Web/Business edition to cache at least 5 GB in memory. More on this below.

This test also allowed some write-rate metrics to be captured.  These are more or less identical to those captured during the earlier tests, i.e. nothing has changed here.


Continue reading

Free Tool: SQL Workload Profiler: For On-Premise and Azure SQL DB

Over the past few weeks I have enhanced a tool I previously developed for gathering and analysing SQL Server Performance Statistics.

Today I have made the tool available via this blog.  It is called SQL Workload Profiler:

20140928_01_Delta      20140928_03_Chart

The tool aims to to provide performance statistics for the queries running in your SQL Server minute-by-minute (or less), together with a quick and simple analysis interface to allow that data to be explored.  The captured granular statistics can also be written to a SQL Table during data collection for saving, more detailed analysis, etc.

Many more details can be found over on the SQL Workload Profiler page.

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.

Continue reading