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

Continue reading

Advertisements

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

Indirect Select Test Overview

The Indirect Select test involves executing a text SQL command to select multiple rows from a pre-populated test table.  The aim of the test is to investigate performance of a read workload.

The Cloud Service fires up a number of worker threads as specified in the test definition. Each worker thread picks a Row Id (primary key value and clustered index field) at random from an in-memory list of primary key values that exist in the database – this is the start of the search range.  The worker thread also chooses a number of rows to scan at random: 100 rows (30% probability), 200 rows (20%), 300 rows (15%), 400 rows (10%) and 600/800/1200/2500/5000 rows (each 5%).  This means the average number of rows examined per request is 660.  A further criteria clause is generated and applied to one of the other fields at random in the test table.  The range used for this other field criteria is also random.  Some examples of WHERE clauses that can be generated include:

  • WHERE (RowId BETWEEN @i1 AND @i2) AND (TinyInt1 BETWEEN @p1 AND @p2)
  • WHERE (RowId BETWEEN @i1 AND @i2) AND (SmallInt2 BETWEEN @p1 AND @p2)
  • WHERE (RowId BETWEEN @i1 AND @i2) AND (Int5 BETWEEN @p1 AND @p2)
  • WHERE (RowId BETWEEN @i1 AND @i2) AND (DateTime2 BETWEEN @p1 AND @p2)
  • WHERE (RowId BETWEEN @i1 AND @i2) AND (Float1 BETWEEN @p1 AND @p2)
  • WHERE (RowId BETWEEN @i1 AND @i2) AND (Money3 BETWEEN @p1 AND @p2)

The worker thread then executes this SQL against the database.  So, in summary, each request selects multiple rows by a primary key range – also the clustered index – and by another field – hence the test name “Indirect Select”.

As in the Direct Select test, all fields from the test table are returned (roughly speaking “SELECT * “).

The worker threads in the UT tests are not limited to a specified request rate. As soon as a SQL 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.

Both the UT and LT Direct Select 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 investigates whether reads from the buffer pool are constrained by edition / service tier. In contrast to these tests, the later Sequential Select and Scale Tests purposefully target reading from the physical disk.

UT Test Results

Overview

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

Continue reading

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

Direct Select Test Overview

The Direct Select test involves calling a stored procedure to select single rows from a pre-populated test table.

The Cloud Service fires up a number of worker threads as specified in the test definition. Each worker thread picks a Row Id (primary key value) at random from an in-memory list of primary key values that exist in the database. The worker thread then calls a simple stored procedure to select that single row (see stored procedure definition at the bottom of the post here).  I.e. each request to the stored procedure directly selects a single row by primary key value – also the clustered index – hence the test name “Direct Select”.

The worker threads in the UT tests are not limited to a specified request rate. As soon as a select 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.

Both the UT and LT Direct Select 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 investigates whether reads from the buffer pool are constrained by edition / service tier.  In contrast to these tests, the later Sequential Select and Scale Tests purposefully target reading from the physical disk.

UT Test Results

Overview

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

Continue reading

Microsoft Azure SQL Database Performance Tests: Bulk Load

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

Bulk Load Test Overview

The Bulk Load tests involve using the .NET SqlBulkCopy class to load data. Loading batches of rows in this way is significantly more efficient than loading single rows via stored procedure. This also allows the Bulk Load tests to provide a better estimate of the log write rate limit of each service tier.

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 load the rows in batches into the test database.

The worker threads in the UT tests are not limited to a specified request rate. As soon as each bulk load batch of rows has completed another is generated (i.e. each request = one batch = multiple rows). The worker threads in the LT tests are constrained to generate batch load requests at a limited rate specified in the test definition. The LT tests gradually increase the batch size over the course of a number of tests. This is in contrast to the other LT tests which increase the request rate over the course of a number of tests. A few different permutations of thread count and batches per second are also tested.

UT Test Results

Overview

Results from the 30 UT Bulk Load 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-C-UT-Rows

Continue reading

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

Continue reading

Microsoft Azure SQL Database Performance Tests: Database Import, Copy and Restore – Write Rates

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.

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

Introduction

Running these performance tests required deploying some large pre-populated databases (particularly for the Sequential Select and Scale Tests). These databases were deployed via a variety of means – with the dual objectives of minimising deployment time whilst also capturing some useful performance metrics.

So, before we delve into the detail of the performance tests, this post provides a digression into the results of the database deployments and the information inferred from them.

Continue reading

Microsoft Azure SQL Database Performance Tests: Test Architecture, Test Types and Test Configurations

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.

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

Performance Test Architecture

All elements of the test set up run in Azure, in the same region (Western Europe).

Image

A Cloud Service (Worker Role) ran each test. This reads the definition of each test from a shared control database before executing the test against a target test database – i.e. preparing the database with test data, generating requests of the type and rate specified in the test definition, then cleaning up the test database.

The cloud service can scale to whatever number of request generating threads are required (provided a large enough cloud service instance is configured in Azure).

The cloud service also tracks and captures details of the test (response timings, data volumes, etc) and records this information in the control database.

Fore more details, please continue reading below.

Continue reading

Microsoft Azure SQL Database Performance Tests: Background and Overview

Background

At the end of April Microsoft announced changes to Azure SQL Database: see here and here. As well as many new features being announced, by far the biggest change was the impending end of the current Web/Business offering and the announcement of the new Basic/Standard offerings to go with the previously announced Premium offering.

The fundamental change is that Microsoft is moving away from charging by database size (per GB) as the current Web / Business offerings do – instead to charging by performance for the Basic / Standard / Premium offerings. Microsoft are describing the different performance levels of these new offerings in terms of a largely immeasurable new acronym, Database Throughput Unit (DTU) and some accompanying transaction rate numbers. See here and here. Upon reviewing this information, I was unclear just what throughput I could get for my workloads in the different service tiers. Carrying out some performance tests seemed like the best plan.

So, this is the first in a series of posts that document my tests. I’ll start by describing my objectives below.  The next post will describe the test set up. The following posts will then delve into the results of each type of test.  The final post summarises all the results in one place if you want to jump right to that.

At the top of each post are links to the next/previous articles in the series.  Or use the full contents list below.

Quick Navigation:  >> Next post in series >>

Short Of Time – Highlights

If you only have a couple of minutes, just read the Summary.

If you have a little more time, then probably the best bits to read are:

  • Bulk Load – to see roughly the maximum write rates per edition/tier,
  • Sequential Reads – to see roughly the maximum read rates per edition / tier,
  • Scale Tests – to see how the different editions / tiers handle one example workload when read limits are involved,
  • Summary – summary of results, rates, concluding thoughts and wrap-up.

All Posts In This Series

Overview Posts

  1. Background and Overview of Performance Tests (this post).
  2. Test Architecture, Test Types and Test Configurations – Read

Focus:  Write Rates

  1. Database Import, Copy and Restore – Read
  2. Inserts – Read
  3. Bulk Load – Read

Focus:  Read Rates from the Buffer Pool

  1. Direct Selects – Read
  2. Indirect Selects – Read
  3. Mini Sequential Selects – Read

Focus:  Write Rates and Read Rates from the Buffer Pool

  1. Updates – Read
  2. Inserts and Deletes – Read
  3. Inserts, Direct Selects, Updates and Deletes – Read
  4. Inserts, Direct and Indirect Selects, Updates and Deletes – Read

Tests with Larger Databases – Focus:  Write Rates and Read Rates from Physical Disk

  1. Sequential Selects – Read
  2. Scale Tests:  Inserts, Selects, Updates and Deletes – Read

Wrap-Up

  1. Summary – Read

Azure SQL Database Throttling

Azure SQL Database Web and Business Editions manage resources rather opportunistically. These service tiers do not apply strict per database limits, capacity can to quite a large extent be utilised if it is available. Should a server start to become overloaded, an escalating series of throttling actions occur as described here. This can lead to sudden changes in SQL Database performance – i.e. due to neighbours with a heavy workload. Database failovers can also occur to as the platform attempts to equalise load and performance – though in my experience, in recent months performance has stabilised and failovers are less common than six months to a year ago.

Life is different in the Basic, Standard and Premium Service Tiers. In order to guarantee performance, Azure SQL Database limits the performance of databases within the Basic, Standard and Premium Service Tiers in at least the following ways:

  • Log Write Rate – i.e. the maximum amount of data that can be written into the database transaction log per second,
  • Physical Disk Read Rate – i.e. the maximum amount of data that can be read from the database data files per second,
  • CPU time – i.e. the maximum amount of CPU time each database is granted per second.

It is possible that some form of memory limit exists or will exist too which will likely become clearer during the preview.

The performance tests I have conducted explored these limits and compared the capabilities of the different tiers.

Objectives of Performance Tests

  • Estimate/measure the performance limits of each service tier in more meaningful units that DTUs
    • Maximum write rates
    • Maximum read rates
    • These terms will be more clearly defined in later posts.
    • (It would be helpful if Microsoft published these, as it would allow customers to quickly rule in/out certain service tiers for their workloads purely on the basis of these two limits).
  • Perform some direct comparisons between the existing Web / Business service tiers and the newer Basic / Standard / Premium service tiers.
  • Keep the tests relatively simple so that the results are relatively easily and intuitively understandable even if this means the workloads are a little abstract (i.e. roughly speaking, the opposite of Microsoft’s Azure SQL Database Benchmark described here). As a developer I prefer to understand the basic boundaries (in well-defined numerical terms) of a platform. I can then design an appropriate solution within those boundaries.
  • Apply a reasonably heavy workload to Azure SQL Database in order to establish roughly where the limits are, but not be absolutely excessive – particularly in terms of Web/Business, to not generate a disruptive load (i.e. be a noisy neighbour) causing hard throttling errors.
  • Note any other differences in behaviour that we come across along the way.

I have focussed less on the CPU performance limits of each tier in the tests. I have no tests designed to push the CPU limits. The tests observe if CPU throttling is occurring but are more focussed on read/write rates. I made this decision because of the nature of my current Azure SQL DB workloads and limited time for this mini-project. I may revisit this later.

One consequence of focussing on relatively simple tests is that none of the tests involved tables with non-clustered indexes. “WHERE” clauses were either against only the clustered index of each table, or against a range of the clustered index plus (i.e. “AND”) one other field with no covering index.  I.e. Clustered Index Seeks.

Of course, performance under non-clustered indexes is important, but I wanted to test at least initially under simple test conditions and time of course limits testing all the permutations you/I might like.  So I ignored non-clustered indexes.  I may revisit this later.

Summary of Performance Tests

  • Tests cover Web, Business, Basic, Standard S1, Standard S2, Premium P1 and Premium P2.
  • No tests conducted against P3 service tier (too costly).
  • Tests cover Inserts, Bulk Load, Selects (from buffer pool and from disk), Updates and Deletes.
  • Tests performed between 10th and 29th of June 2014, i.e. well after the performance increases announced on the 19th May here.
  • Tests performed in the Western Europe Azure Data Centre.

Important Notes

To be clear, I don’t claim that these are the best performance tests for Azure SQL DB. The results may or may not apply to your workload, but I hope I have given enough information for readers to determine that themselves.

I chose these tests because they have helped me get a feel for the new service tiers and because they are relevant to my workload. I am sharing the results because they may be relevant or helpful for other people.  Of course SQL Server runs workloads of many different types at many different scales, so I don’t claim the information here is widely applicable.

Of course, the new service tiers are still in preview. Performance is likely to change during the preview. I deliberately waited a while after the start of the preview as I guessed performance would be more variable initially especially. It is still likely to be tweaked by Microsoft as the preview progresses.