Microsoft Azure SQL Database Performance Tests: Inserts, Direct and Indirect Selects, Updates and Deletes

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, Direct and Indirect Selects, Updates and Deletes Test Overview

This test combines the activities of the earlier Inserts and Deletes, Direct Selects, Indirect Selects and Updates tests. Please refer to those posts for a more detailed description of these activities.

The type of request generated by each worker thread at any point in time is randomly selected, based on the following probabilities:

  • Inserts – 20%
  • Selects – 30% – which breaks down as 75% Direct Selects and 25% Indirect Selects
  • Updates – 30%
  • Deletes – 20%

As in those earlier tests, the worker threads in the UT tests are not limited to a specified request rate while the worker threads in the LT tests are constrained to generate requests at a limited rate specified in the test definition.

The UT tests run against only a small set of test data. This means the entire data set generally exists in the SQL Server Buffer pool (the test table is pre-populated immediately before the test begins). This test therefore primarily investigates write rates combined with read rates from the buffer pool. In contrast to these tests, the later Scale Tests include all of the actions here but acting on data that is not always resident in the buffer pool.

UT Test Results

Overview

Results from the 30 UT 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-I-UT-Rows

20140630-I-UT-MB

The two charts are similar because the average 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 slightly 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.

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.

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-I-UT-Metrics1

20140630-I-UT-CPU120140630-I-UT-LogW120140630-I-UT-DiskR1

Performance of the new tiers is again consistent.  The throughput appears a little more ragged because a slight random increase in the number of Indirect Select requests can have a dramatic effect on row counts and data volumes.  These requests also tend to slightly reduce the pressure on the log write quota, though it is still a relatively dominating control on overall throughput.

Std2 to P2 Tiers

20140630-I-UT-Metrics2

20140630-I-UT-CPU2

20140630-I-UT-LogW2

20140630-I-UT-DiskR2

Business

20140630-I-UT-Metrics3

20140630-I-UT-CPU3

20140630-I-UT-LogW3

20140630-I-UT-DiskR3

The SQL Server performance data (from sys.resource_stats) has a few data points missing on these charts. It appears as though a bug or infrastructure fault within Azure was preventing some usage data being processed correctly into the view.

Nonetheless, these profiles show the much more variable nature of the Business Edition. Those tests where multiple threads are executing show considerably more variable performance, sometimes varying wildly.

LT Test Results

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

Basic Tier

20140630-I-LT-2

As the request rate is increased, the CPU utilisation and log write utilisation increase. At around 100 rows per second, we have hit the log write limit and the database is not able to keep up with the request rate. All of the data for these tests was present in the buffer pool. Disk Read quota had no impact.

Performance is very consistent throughout.

S1 Tier

20140630-I-LT-3

This is a rather mixed looking chart.  The tests that were performed at 120 and 160 requests per second are showing behaviour inconsistent with the other tests.  The cause(s) is/are not immediately clear from this chart (i.e. none of SQL CPU, log write or disk read limits were close to being hit).  The cause is also not clear from the other diagnostic data collected during the tests.  The cloud service request generator CPU utilisation was only around 10% insufficient CPU power in the request generator VM is unlikely to be the cause.

Web Edition

20140630-I-LT-1

This chart shows good consistency from Web Edition, with linear performance up to 120 requests per second, also consistent with (and actually slightly better than) the earlier UT test.

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 40 requests per second, but we can actually continue increasing our request rate to nearly three times this “maximum” – i.e. to 130 requests per second – before we actually see a significant number of requests failing to be processed.

Inserts, Direct and Indirect Selects, Updates and Deletes Test Conclusions

The combined tests have shown that, for a workload of purely stored procedure based row-by-row inserts, direct and indirect selects, updates and deletes (of average row size 400 bytes), where the data is already in the buffer pool, performance of the new S2 / P1 tiers generally equals the current Business Edition. P2 significantly outperforms Business for this workload.

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.1 0.2 0.2 0.2 0.8 0.1 0.1 0.4 0.8 1.2

Appendix – UT Test Results

Configuration Avg Rows Per Second Avg MB Per Minute
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 1046 944 912 22.1 19.9 19.2
Business 1T 1407 1381 1043 29.6 29.1 22.0
Business 2T 2492 2629 1730 52.4 55.3 36.5
Business 4T 3950 4071 2643 83.3 85.8 55.7
Business 8T 5497 6141 5136 115.7 129.3 108.2
Basic 1T 814 829 864 17.1 17.5 18.2
Std S1 1T 1468 1389 1526 31.0 29.2 32.1
Std S2 2T 3067 2880 2673 64.6 60.7 56.4
Prem P1 4T 4513 4400 4362 95.0 92.6 91.9
Prem P2 8T 8415 8648 8551 177.4 182.2 179.8
Configuration SQL Avg Log Write % SQL Avg Disk Read % SQL Avg CPU %
UT1 UT2 UT3 UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 100.0 100.0 100.0 0.0 0.0 0.0 30.4 28.4 27.2
Business 1T 100.0 100.0 100.0 0.0 0.0 0.0 35.4 34.8 28.0
Business 2T 100.0 100.0 100.0 0.0 0.0 0.0 63.1 67.0 40.2
Business 4T 100.0 100.0 100.0 0.0 0.0 0.0 99.3 100.0 68.7
Business 8T 100.0 100.0 100.0 0.0 0.0 0.0 100.0 100.0 100.0
Basic 1T 94.5 90.6 94.9 0.0 0.0 0.0 66.3 62.7 64.6
Std S1 1T 78.5 77.8 86.5 0.1 0.0 0.0 37.1 35.9 35.4
Std S2 2T 85.8 81.0 76.7 1.3 0.4 0.2 34.9 32.2 39.6
Prem P1 4T 99.5 99.1 99.3 0.0 0.0 0.0 18.8 20.1 16.7
Prem P2 8T 88.1 96.3 96.4 0.0 0.0 0.0 19.6 22.2 22.0
Configuration Cloud Svc Avg CPU Error Count
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 6.2 5.8 5.8 0 0 0
Business 1T 5.3 4.5 3.5 0 0 0
Business 2T 7.9 8.2 6.4 0 0 0
Business 4T 11.9 12.2 9.0 0 0 4
Business 8T 9.7 10.8 9.1 1 0 0
Basic 1T 5.4 5.6 5.9 0 0 0
Std S1 1T 9.0 9.3 9.5 0 0 0
Std S2 2T 10.6 9.4 8.7 0 0 0
Prem P1 4T 13.4 13.5 13.9 0 0 0
Prem P2 8T 26.5 27.0 27.3 0 0 0

Appendix – LT Test Configuration

SQL Edition Web Basic Std1
Cloud Svc Inst. Size A0 A0 A0
Req. Gen. Thread Count 1 1 1
Initial Test Data (MB) 0.25 0.25 0.5

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 20 181 196 3.8 4.1
1 40 364 397 398 7.6 8.4 8.4
1 50 467 494 9.8 10.4
1 60 558 594 550 11.8 12.5 11.6
1 70 690 682 14.6 14.4
1 80 778 761 742 16.4 16.0 15.6
1 90 866 832 18.2 17.5
1 100 935 843 927 19.7 17.8 19.5
1 110 1077 873 1053 22.7 18.4 22.1
1 120 1164 881 955 24.5 18.6 20.1
1 130 1158 1137 24.4 23.9
1 140 1070 1278 22.6 26.9
1 150 1165 1467 24.5 30.9
1 160 1205 1164 25.3 24.5
1 170 1426 30.0
1 180 1495 31.5
1 190 1471 31.1
1 200 1297 27.3
Threads Req. Per Sec SQL Avg Log Write % SQL Avg Disk Read % SQL Avg CPU %
Web Basic Std 1 Web Basic Std 1 Web Basic Std 1
1 20 54.5 22.1 0.0 0.0 6.3 14.2
1 40 100.0 43.9 22.0 0.0 0.0 0.4 11.6 27.3 15.1
1 50 100.0 54.7 0.1 0.0 14.2 33.7
1 60 100.0 65.4 33.1 0.1 0.0 0.0 16.9 40.5 22.0
1 70 100.0 76.7 0.0 0.0 19.8 46.7
1 80 100.0 87.1 41.9 0.0 0.0 0.0 22.2 52.5 26.9
1 90 100.0 96.9 0.0 0.0 24.9 59.5
1 100 100.0 95.0 54.5 0.0 0.0 0.1 27.2 58.1 35.8
1 110 100.0 97.0 60.3 0.0 0.0 1.2 29.6 60.1 40.3
1 120 100.0 95.8 57.0 0.0 0.0 0.0 32.8 58.7 36.8
1 130 100.0 64.3 0.0 0.1 33.6 40.9
1 140 100.0 69.8 0.0 0.1 31.6 43.6
1 150 100.0 78.3 0.0 0.2 33.5 49.7
1 160 100.0 65.9 0.0 0.4 34.1 41.2
1 170 77.0 0.1 48.1
1 180 80.2 0.7 51.3
1 190 78.2 0.1 50.6
1 200 67.9 0.0 42.6

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