Microsoft Azure SQL Database Performance Tests: Sequential Selects

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 >>

Sequential Select Test Overview

The Sequential Select test involves reading an entire table or tables to get a simple estimate of maximum read rates.  The data is read from each table sequentially (i.e. roughly speaking SELECT * with no ORDER BY).  Of course, data access at the file level may well not be sequential.

The test involves using a newly restored database.  The database is sized at 4.5 GB for Web Edition, 10 GB for Business Edition, 2 GB for the Basic Tier and 36 GB for the Standard and Premier tiers.

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 for each tier.

Test Results

Overview

Results from the Sequential Select tests are shown in the two charts below.  These rates represent the “read-limited” rates, i.e. when the disk read % metric was reporting 100%.  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.

2014-07-04-SeqSel-Rows

2014-07-04-SeqSel-MBperMin

This result demonstrates the clear differences in performance between tiers.  It is interesting to note where the new Premium tier performance levels are positioned against Web/Business, i.e. P1 is lower than Web/Business, P2 significantly higher.

In hindsight, it would have been useful to also have tested the Business Edition with a 2T test (on an A2 sized instance rather than an A1), since this might have further increased the read rate of Business Edition.

We can now also compare the read rates from disk with the read rates for data already in the buffer pool:

2014-07-04-SeqSel-BufferPoolvsPhysDisk

It is interesting to note that Basic edition seems to outperform S1.  That slight difference actually equates to about 11 MB difference per minute.  This difference is discussed further below.

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 is not useful and so is omitted.

Basic Tier

2014-07-04-SeqSel-Metrics1

The large jump at minute 42 is very likely due to the entire database now being cached in the buffer pool.  The disk read profile below will allow us to confirm this.  Nonetheless, it suggests that there is no buffer memory limit enforced per database.  Whether this behaviour will continue as the preview progresses and into GA when the service becomes more heavily used is debatable.

 Basic and S1 Tiers

2014-07-04-SeqSel-Metrics2

This is an interesting chart.  At first glance it appears to show the Basic edition generally slightly outperforms S1 as shown in the average charts earlier.

However… the peak in the S1 read rate in the first five minutes suggests data read quota is allocated in blocks per unit of time.  And thus, provided users stay within the expected transaction rate, read performance may be quite good.  Step outside that quote however and performance is very heavily constrained.  The burst read rate is about three times the sustained read rate.

Basic to S2 Tiers

2014-07-04-SeqSel-Metrics3

Again, a higher performing burst appears to be present initially in the S2 profile, lasting for around eight minutes, after which performance becomes more variable and generally lower.

S1 to P2 Tiers

2014-07-04-SeqSel-Metrics5

Again, slightly higher performing bursts and present in the P1 and P2 profiles.  On this chart, the apparent initial bursts are quite pronounced for all tiers.

Web / Business

2014-07-04-SeqSel-Metrics6

Another interesting chart.  Business on and minute by minute basis seems slightly more consistent than the new service tiers, though as ever there is a risk of a sudden and significant drop in performance, such as around minutes 16 and 58 in the chart above.

Web / Business, P1 and P2

2014-07-04-SeqSel-Metrics7

Plotting Web/Business and P1/P2 on the same charts allows this comparison to be made more easily.

It may be that the new tiers have two “pools” of read quota – a higher and more consistent level of performance that is allocated according to the quota of the tier – and once this has been fully consumed performance defaults back to a lower  and less guaranteed performance level.  As blocks of the guaranteed quota are allocated, performance rises for a period and then falls back again once that is consumed.  This is of course entirely speculation but would explain the more lumpy nature of the performance profiles for the new tiers.

If read rate quota were enforced as a single hard limit then it would seem difficult for the Standard Editions to support anything remotely approaching 250GB – e.g. even for a well designed, well indexed database – just loading indexes at 0.5MB per second is going to take a  long time relatively speaking.  The allowance for short term bursts would seem the obvious solution to this problem.

Of course, this initial peak may be nothing more than a curious side-effect of my test apparatus, however, if that were the case, I would probably expect the same behaviour to be present in the Web/Business tests – where it is not really present.

Disk Read Utilisation

2014-07-04-DiskRead1

This chart shows that Web edition appears to have entirely cached the 4.5GB test database after ten minutes and Business Edition has also cached the entire 10 GB database after around twenty minutes.

2014-07-04-DiskRead2

As expected, this test shows all of the tests on the new service tiers were running at 100% disk read rate utilisation.  The Basic Tier database has been entirely cached after around 40 minutes, consistent with the sudden increase in read rate for that database seen in the earlier charts at around the same point in time.

CPU Utilisation

2014-07-04-CPU1

It is interesting to see some inconsistency in the reported read rate usage (with no apparent similar difference seen in the observed read rates), with two tests reporting nearly 100% throughout, with the remaining tests much less.  These tests were all performed on the same day.

2014-07-04-CPU2

CPU quota utilisation is low across all of the newer service tiers, with the exception of the Basic tier once after around 40 minutes.  At this point, since the entire database is cached, performance goes from being constrained by disk read quota  to CPU quota.

Sequential Select Test Conclusions

The Sequential Select tests have illustrated how the data read rate from physical disk has been heavily constrained in the new service tiers.  The tests have hinted at the possible existence of a higher performance level when running under quota that seems to exist most dramatically in the Std S1 performance level.

More specifically, for the initial burst:

Service Tier Phys Disk Burst Limit Phys Disk Burst Comparison
Web / Business = 550 MB per minute
Basic = 39 MB per minute 7.1% of Web / Business
Standard S1 = 75 MB per minute 14% of Web / Business
Standard S2 = 210 MB per minute 38% of Web / Business
Premium P1 = 460 MB per minute 84% of Web / Business
Premium P2 = 900 MB per minute 160% of Web / Business

For a sustained read:

Service Tier Phys Disk Sustained Limit Phys Disk Sustained Comparison
Web / Business = 550 MB per minute
Basic = 39.3 MB per minute 7.1% of Web / Business
Standard S1 = 28.7 MB per minute 5.2% of Web / Business
Standard S2 = 143.6 MB per minute 26% of Web / Business
Premium P1 = 400.4 MB per minute 73% of Web / Business
Premium P2 = 766.2 MB per minute 140% of Web / Business

It should be noted that the Web / Business rate given here is for a single thread reading from the database.  Due to the opportunistic nature of those editions, it is possible that opening additional threads would yield a higher rate.  The rates given for the newer service tiers are likely to be close to the maximum rates for those tiers due to SQL Database reporting all tests as running at 100% utilisation of available disk read quota.

Appendix – Test Configuration

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

Appendix – Test Results

Configuration Avg Rows Per Second Avg MB Per Minute
SQ1 SQ2 SQ3 SQ1 SQ2 SQ3
Web 1T 25714 28729 27077 553.2 618.1 582.1
Business 1T 24664 24667 22429 530.6 530.7 482.5
Basic 1T 1820 1833 1833 39.1 39.4 39.4
Std S1 1T 1412 1229 1359 30.3 26.4 29.2
Std S2 2T 6661 6291 7095 143.1 135.2 152.4
Prem P1 2T 18767 17742 19399 403.2 381.2 416.8
Prem P2 4T 36151 32589 38243 776.8 700.2 821.7
Configuration SQL Avg Disk Read % SQL Avg CPU %
SQ1 SQ2 SQ3 SQ1 SQ2 SQ3
Web 1T 9.1 8.3 9.1 49.8 99.7 49.9
Business 1T 25.0 25.0 30.6 47.3 44.1 100.0
Basic 1T 69.8 73.7 68.6 36.2 34.2 42.6
Std S1 1T 99.9 100.0 100.0 3.5 3.8 3.5
Std S2 2T 99.9 100.0 100.0 4.1 3.6 3.6
Prem P1 2T 99.9 100.0 100.0 4.5 4.5 4.6
Prem P2 4T 97.5 100.0 100.0 4.6 4.1 5.5
Configuration Cloud Svc Avg CPU Error Count
SQ1 SQ2 SQ3 SQ1 SQ2 SQ3
Web 1T 94.2 99.4 99.2 0 0 0
Business 1T 93.1 93.3 83.1 0 0 0
Basic 1T 23.7 20.8 15.2 0 0 0
Std S1 1T 5.3 4.3 5.2 0 0 0
Std S2 2T 12.8 12.6 14.0 0 0 0
Prem P1 2T 34.7 34.5 36.6 0 0 0
Prem P2 4T 73.5 30.5 35.1 0 0 0
Advertisements

2 comments

  1. Pingback: sql azure vs azure VM with SQLServer Express – segmentfault


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