Microsoft Azure SQL Database Performance Tests: Inserts

Performance Testing Series

This post is part of a series of blog posts about my performance tests in Azure SQL Database. For the first post in this series (and links to all of the other posts) please see here.  For a summary of all the results please see here.

For a general overview of the test architecture, test components and test types, please see here.

Quick Navigation:      << Previous post in series <<      >> Next post in series >>

Inserts Test Overview

The Inserts test involves calling a stored procedure to insert rows into a test table. The Cloud Service fires up a number of worker threads as specified in the test definition. The worker threads generate random data to be inserted. Row Id values (the primary key field) are generated sequentially. The worker threads then call a simple stored procedure to insert rows one-by-one (see stored procedure definition at the bottom of the post here).

The worker threads in the UT tests are not limited to a specified request rate. As soon as an insert stored procedure call has completed another is generated. The worker threads in the LT tests are constrained to generate requests at a limited rate specified in the test definition. The LT tests gradually increase the rate over the course of a number of tests.

UT Test Results

Overview

Results from the 30 UT Inserts tests are shown in the two charts below.  In these charts, the “T” values are the number of worker threads generating requests against the database, e.g. “Business 4T” = a test against Business edition, running with 4 threads continuously generating requests.

20140630-B-UT-Rows

 20140630-B-UT-MB

As you may have noticed, the two charts above are more or less the same chart – because on average the row size was around 400 bytes throughout.  The data volume figures here are based on the data content (i.e. summing the sizes of the data values, according to the number of bytes each data value requires in the SQL Server Data Type scheme but ignoring internal SQL Server overheads).

Several different tests of the current Web/Business edition were carried out.  The Web 1T test was carried out on a A1 sized cloud instance, the Business 1T test on an A2 sized instance.  This explains the higher throughput on the Business 1T test.  As the number of threads was increased in the Business edition tests, the throughput increases though is variable.

During two of the three Business 8T tests a short outage occurred (one during the second test and one during the third).  This is likely a failover of the test database as Azure balances workload.  Interestingly, one of those outages occurred during the second of the Business 8T tests (the highest performer of the three) indicating the presence or absence of short outages had no significant effect on the results here.  Nonetheless, the fact the failovers occurred in two out of three of the Business 8T tests (but none in any other test) indicates we were very likely approaching the maximum tolerable load.

In contrast to the Business Edition tests, performance of the new service tiers is very consistent.  From these results S2 generally equals or outperforms Business Edition.  Interestingly, for this workload, the gap between S2 and P1 is not all that significant, but P2 is substantially better than P1 and towers above Business Edition.

I will demonstrate below that all of the test results for the new service tiers shown above are limited by the SQL Server Log Write Rate limit, i.e. this is the determining factor in the performance.

Test Profiles

The charts below show the performance profiles over the lifetime of the tests in the charts above.  Since 30 lines is rather a lot to fit onto one chart, data for the different editions / tiers has been split across several charts.

It is also worth noting that due to the way the Azure SQL Database logging into sys.resource_stats works, data is only available at five minute intervals and the last data point in each test (which would have been plotted as minute 27 in these charts) is not useful and so is omitted.

Basic to P1 Tiers

20140630-B-UT-Metrics1

20140630-B-UT-CPU1

20140630-B-UT-LogW1

Performance of the new tiers is very consistent – other than a blip towards the end of one of the S2 tests.  The charts show that these tests weren’t constrained on CPU but were clearly constrained on log write quota.

Std2 to P2 Tiers

20140630-B-UT-Metrics2

20140630-B-UT-CPU2

20140630-B-UT-LogW2

Again, these profiles illustrate good consistency, with the exception of the same S2 blip in one of the three S2 tests.

Business

20140630-B-UT-Metrics3

20140630-B-UT-CPU3

20140630-B-UT-LogW3

These profiles show the much more variable nature of the Business Edition.  Those tests where multiple threads are executing show considerably more variable performance.  Crudely speaking, this is the Wild Wild West of performance.

In addition, the Log Write profile looks suspiciously odd, being at 100% for all data points in all tests.  More on this below.

LT Test Results

Three series of LT tests were carried out, against the Web, Basic and S1 editions / tiers.

Basic Tier

The chart below illustrates how the performance of the Basic Tier varies as the workload (number of requests per second) is increased.  A 30 minute test was conducted at each request rate.

20140630-B-LT-1

As the request rate is increased, the CPU utilisation and log write utilisation increase.  At around 70 rows per second, we have hit the log write limit and the database is not able to keep up with the request rate.

Performance is very consistent throughout.

S1 Tier

20140630-B-LT-2

Again, a similar type of profile to the Basic tier database – in this case we hit the log write rate limit at 140 rows per second.

Web Edition

20140630-B-LT-3

The web edition is slightly less consistent – i.e. we start to miss requests in a more rough and ready manner.

It is interesting to note that this chart also proves the well known fact that there is no clearly enforced log write limit per database in the Web Edition.  I.e. we apparently hit the reported log write limit at 30 requests per second, but we can actually continue increasing our request rate to four times this “maximum” – i.e. 120 requests per second – before we actually see a significant number of requests failing to be processed.

Inserts Test Conclusions

The Insert Tests have shown that, for a workload of purely stored procedure based row-by-row inserts (of average row size 400 bytes), performance of the new S2 / P1 tiers generally equals or exceeds the current Business Edition.  P2 significantly outperforms Business for this workload.  More specifically:

Service Tier Insert Limit Insert Limit Comparison
Web / Business = 4.8 MB per minute
Basic = 1.4 MB per minute 29% of Web / Business
Standard S1 = 2.7 MB per minute 58% of Web / Business
Standard S2 = 5.5 MB per minute 120% of Web / Business
Premium P1 = 7.0 MB per minute 150% of Web / Business
Premium P2 = 13.5 MB per minute 280% of Web / Business

The new service tiers also show an impressive level of consistency compared to the Business edition.

Appendix – UT Test Configuration

SQL Edition Web Bus. Bus. Bus. Bus. Basic Std1 Std2 Prem1 Prem2
Cloud Svc Inst. Size A1 A2 A2 A2 A3 A1 A1 A2 A2 A2
Req. Gen. Thread Count 1 1 2 4 8 1 1 2 4 8
Initial Test Data (MB) 0 0 0 0 0 0 0 0 0 0

Appendix – UT Test Results

Configuration Avg Rows Per Second Avg MB Per Minute
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 87 82 87 1.85 1.75 1.85
Business 1T 127 124 127 2.71 2.67 2.71
Business 2T 182 208 152 3.91 4.44 3.24
Business 4T 292 277 144 6.22 5.91 3.07
Business 8T 163 296 172 3.47 6.31 3.66
Basic 1T 62 66 66 1.31 1.41 1.40
Std S1 1T 124 132 131 2.64 2.80 2.79
Std S2 2T 254 269 248 5.42 5.74 5.29
Prem P1 4T 316 333 332 6.75 7.11 7.08
Prem P2 8T 619 646 635 13.21 13.79 13.57
Configuration SQL Avg Log Write % SQL Avg CPU %
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 100.0 100.0 100.0 29.2 24.4 26.8
Business 1T 100.0 100.0 100.0 39.2 31.3 33.9
Business 2T 100.0 100.0 100.0 48.6 53.7 47.9
Business 4T 100.0 100.0 100.0 79.4 69.1 30.1
Business 8T 100.0 100.0 100.0 44.3 75.5 53.8
Basic 1T 95.5 96.5 95.9 59.7 52.1 54.1
Std S1 1T 96.0 96.3 96.0 35.3 31.6 34.1
Std S2 2T 98.7 99.3 88.4 34.8 35.9 29.9
Prem P1 4T 99.7 99.7 99.8 14.4 13.8 13.5
Prem P2 8T 97.1 97.5 95.4 18.9 19.5 20.6
Configuration Cloud Svc Avg CPU Error Count
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 2.4 2.1 2.3 0 1 0
Business 1T 2.2 2.3 1.9 0 0 1
Business 2T 2.4 3.0 3.1 0 0 0
Business 4T 3.7 3.3 2.6 0 0 4
Business 8T 2.1 3.1 1.7 0 8 8
Basic 1T 2.1 2.0 2.1 0 0 0
Std S1 1T 3.5 3.5 3.5 0 0 0
Std S2 2T 3.8 4.3 3.6 0 0 0
Prem P1 4T 4.2 3.8 4.3 0 0 0
Prem P2 8T 7.0 7.4 7.3 0 0 0

Appendix – LT Test Configuration

SQL Edition Web Basic Std1
Cloud Svc Inst. Size A1 A1 A1
Req. Gen. Thread Count 1 1 1 – 2
Min Req. Gen. per Sec. 1 1 1
Max Req. Gen. per Sec. 200 100 200
Initial Test Data (MB) 0 0 0

Appendix – LT Test Results

Threads Req. Per Sec Avg Rows Per Second Avg MB Per Minute
Web Basic Std 1 Web Basic Std 1
1 1 1 1 1 0.02 0.02 0.02
1 10 10 10 0.21 0.21
1 20 20 20 20 0.42 0.43 0.43
1 30 30 30 0.64 0.64
1 40 40 40 40 0.84 0.85 0.85
1 50 50 50 1.06 1.07
1 60 59 60 60 1.26 1.27 1.28
1 70 69 68 1.47 1.45
1 80 80 68 80 1.70 1.44 1.71
1 90 89 68 1.90 1.45
1 100 98 68 97 2.10 1.44 2.07
1 110 103 2.20
1 120 93 1.99
1 130 105 2.24
1 140 103 2.19
1 150 103 2.20
1 160 107 2.29
1 170 111 2.37
1 180 113 2.41
1 190 109 2.33
1 200 116 134 2.47 2.85
2 100 100 2.13
2 120 120 2.56
2 140 135 2.88
2 160 135 2.88
2 180 135 2.89
2 200 135 2.88
Threads Req. Per Sec SQL Avg Log Write % SQL Avg CPU %
Web Basic Std 1 Web Basic Std 1
1 1 3.6 1.5 0.7 0.3 0.8 0.4
1 10 36.3 14.6 3.3 8.1
1 20 72.1 29.2 14.6 6.1 15.5 5.5
1 30 100.0 43.9 8.9 23.3
1 40 100.0 58.0 29.3 11.6 30.2 10.7
1 50 100.0 73.1 14.5 37.6
1 60 100.0 87.3 43.9 16.9 44.0 15.9
1 70 100.0 99.5 19.8 51.0
1 80 100.0 99.3 58.5 23.1 53.0 20.8
1 90 100.0 99.6 25.5 51.2
1 100 100.0 98.9 72.9 27.0 50.0 23.2
1 110 100.0 29.3
1 120 100.0 27.7
1 130 100.0 29.9
1 140 100.0 29.1
1 150 100.0 28.6
1 160 100.0 29.5
1 170 100.0 31.5
1 180 100.0 31.7
1 190 100.0 30.8
1 200 100.0 98.4 34.0 34.2
2 100 74.4 26.3
2 120 89.1 31.2
2 140 99.8 36.0
2 160 99.9 36.8
2 180 99.8 36.7
2 200 99.8 36.8

The Requests per Second maximum applies in total, not per thread.

Advertisements

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