Microsoft Azure SQL Database Performance Tests: Mini 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 >>

Mini Sequential Selects Test Overview

The Sequential Select test involves reading an entire table or tables to get a simple estimate of maximum read rates.  The “Sequential” in the name of the test refers to the nature of the read from the table i.e. roughly speaking SELECT * with no ORDER BY clause – of course this may or may not actually be a sequential read at the file level.

This test, the Mini Sequential Selects test, performs the same operation on a much smaller set of 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 sequential read rates from the buffer pool.

In contrast to these tests, the later (Full) Sequential Select tests, perform the same action against a much larger database for which none of the data is in the buffer pool.

As in the earlier tests, the worker threads in the UT tests are not limited to a specified request rate.  The data is merely read sequentially repeatedly during the test.  No LT tests were performed.

UT Test Results

Overview

Results from the 30 UT Mini Sequential Select 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-J-UT-Rows

20140630-J-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).

These results may appear confusing on first impression.  They can be easily understood once the CPU power of the Cloud Service is taken into account.   For example, the Business 2T and Business 4T tests were both conducted on an A2 sized Cloud Service VM.  Clearly, two threads are enough to saturate the VM.  The Business 8T test was however conducted on an A3 size VM.  The increased processing power has allowed it to process significantly more data.

Similarly, the Basic and S1 tests were conducted on an A1 sized VM, whilst the S2, P1 and P2 tests were conducted on an A2 sized VM.  For the other UT test types, an A2 VM was sufficient to drive the test, however not in this case (reading rows in the request generator is a relatively intense CPU activity).  So, it is not really a fair comparison between the Business 8T and Prem P2 8T tests!

The full set of VM sizes are given in the appendix of this post.

Ultimately however, what these tests show is that there is no restriction placed on the rate at which data can be read from the buffer pool.

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

20140630-J-UT-CPU1

20140630-J-UT-DiskR1

The resolution of the data throughput data collection was improved after the Basic and S1 tests. and before the S2, P1, P2 and Business tests.  This explains the slightly odd appearance of the data.

The Basic Edition CPU profile shows it was CPU bound.  This explains why the throughput of the Basic 1T UT test is less than the Std S1 1T test in the earlier summary charts.

All of the other service tiers (S2, P1 and P2 as shown below) show the same throughput consistently.

At no point was any significant volume of data read from the disk.

Std2 to P2 Tiers

20140630-J-UT-Metrics2

20140630-J-UT-CPU2

20140630-J-UT-DiskR2

Business

20140630-J-UT-Metrics3

20140630-J-UT-CPU3

20140630-J-UT-DiskR3

Business Edition also shows good consistent throughput.

Mini Sequential Selects Test Conclusions

The Mini Sequential Selects test has shown that the maximum sequential read rate from the buffer pool is, roughly speaking, equal across the different service tiers / editions.  Or, to perhaps be more precise, is not significantly limited according to the edition / tier of SQL Database.

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

Again, in hindsight, the Prem P2 8T test should have been conducted on an A3 sized instance.

Appendix – UT Test Results

Configuration Avg Rows Per Second Avg MB Per Minute
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 27292 27448 26984 586.1 589.8 580.2
Business 1T 26481 26821 26029 570.0 577.7 559.4
Business 2T 40677 40757 40761 875.0 877.7 876.1
Business 4T 40989 40804 41853 881.8 880.2 900.4
Business 8T 70420 71011 70646 1514.7 1526.9 1521.2
Basic 1T 20401 19921 20044 438.2 428.5 430.1
Std S1 1T 23960 25902 28070 514.8 556.9 603.8
Std S2 2T 40101 40457 40484 861.2 872.0 869.5
Prem P1 4T 42612 42782 42791 916.5 922.3 921.0
Prem P2 8T 39418 40029 39304 848.7 861.5 845.8
Configuration SQL Avg Disk Read % SQL Avg CPU %
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 0.0 0.0 0.0 53.4 49.9 55.7
Business 1T 0.0 0.0 0.0 49.7 52.1 96.8
Business 2T 0.0 0.0 0.0 73.9 75.6 74.6
Business 4T 0.0 0.0 0.0 75.2 78.7 79.3
Business 8T 0.0 0.0 0.0 100.0 100.0 100.0
Basic 1T 0.0 0.0 0.0 99.2 99.6 99.7
Std S1 1T 0.0 0.0 0.0 36.4 40.8 44.5
Std S2 2T 0.8 0.0 21.9 20.6
Prem P1 4T 0.0 0.0 0.0 23.2 72.1 51.3
Prem P2 8T 0.0 0.0 0.0 5.7 5.3 5.7
Configuration Cloud Svc Avg CPU Error Count
UT1 UT2 UT3 UT1 UT2 UT3
Web 1T 99.6 99.9 98.7 0 0 0
Business 1T 50.8 51.2 51.4 0 0 0
Business 2T 82.0 81.3 81.7 0 0 0
Business 4T 83.3 83.3 84.5 0 0 0
Business 8T 59.8 60.5 59.8 0 0 0
Basic 1T 72.3 69.2 70.8 0 0 0
Std S1 1T 82.8 93.3 98.6 0 0 0
Std S2 2T 81.6 82.1 83.3 0 0 0
Prem P1 4T 83.7 84.0 83.8 0 0 0
Prem P2 8T 82.8 84.0 84.4 0 0 0
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