Microsoft Azure SQL Database Performance Tests: Updates

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

Update Test Overview

The Update test involves calling a stored procedure to update single rows in 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 generates new values for all fields in the row except the primary key then calls a simple stored procedure to update that single row (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 update 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 Update 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.  In contrast to these tests, the later Scale Tests include updating data that is not always resident in the buffer pool.

UT Test Results

Overview

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

Continue reading

Advertisements

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

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.

Azure SQL Database: Enhancements, Changes to Service Tiers = Significant Price Increase – especially for Small Databases

Update:  6th July 2014:  This post was written based on my first impressions of Microsoft’s announcement of the SQL Database Service Tier Changes.  Since this post was written, based on the performance testing I conducted, I believe more databases may be at risk of suffering a significant price increase.  The performance tests results show S1 and to some extent S2 are quite poorly resourced tiers (esp. on disk read quota) compared to Web / Business.  More details here.

Summary

  • Many improvements in the move from data size to performance based service tiers.
  • New service tiers represent significant price increase for databases in the 1 GB to 6 GB range; Azure uncompetitive and Microsoft risk losing customers to competitors in this database size range.
  • Beyond 10 GB database size Azure remains competitive, for databases over 12 GB new pricing represents a cost reduction (assuming S1 service tier meets transaction rate requirements).

Full Post

I have been mulling over the recently announced changes to the Service Tiers for Microsoft’s SQL Azure Database Service.  I think on the whole they represent very good changes.  Official posts about this here and here.

Whilst not explicitly stated on any official post I’ve seen, the changes look to be orientating the service more around delivering performance and much less around the size of your database.  That’s a very good thing.  There are lots of other improvements too, like an improved SLA, maximum database size increased to 500 GB and new/improved restore and replication options.

Still, part of the new strategy just doesn’t make sense to me.  The pricing for small databases is not competitive and actually represents a very significant price increase for existing customers.  The chart below will hopefully illustrate my point:

Image

The Blue Line represents the current pricing for Web / Business databases – this is based on the size of your database.  The Orange Line represents the new Basic Service Tier, which supports up to 2 GB databases.  The Gray line represents the new S1 Standard Service Tier.  All costs are based on the announced General Availability pricing for the Pay As You Go plans.

In this discussion I am focussing on the comparison between the existing Web databases and the new S1 tier.  Whilst the Basic Tier looks attractive for very small, low/infrequently used workloads, the low size limit and “hourly transaction rate” just doesn’t fit any of my continuously running workloads – which are in the 1 to 10 GB database size range.

The Good News: above a database size of 10 GB, moving from Web to S1 will save you money, assuming the S1 transaction rate meets your requirements.

The Bad News: below 8 GB S1 will cost you more, potentially a lot more in relative terms.

Image

One of my present workloads is an Azure messaging application whose database ranges between 1.5GB and 2.5GB in size in a typical week.  Running it at present costs me about 12-15 USD per month.  Under S1, that is going to become 40 USD.  Wow, around a three-fold increase.

Of course, S1 supports up to 250 GB for the same price, but my messaging application doesn’t actually need anything like that much – this doesn’t feel very much like pay-for-only-what-you-use.

Surely this will also put off some (many?) new customers too from making the jump from Basic to Standard.

Other Options for Small SQL Databases:  Azure and Amazon

In Azure running SQL Express in a VM comes in cheaper, though of course there are disadvantages with that too (lower resiliency and more management effort, just for starters).  Outside of Azure, Amazon’s smallest RDS Cloud offering is significantly cheaper (around 28 USD per month).  And Amazon offer a whole year of that service for free to new customers.

It feels to me like Microsoft needs an extra tier here – something like a “mini-S1”?

Bottom line:  40 USD per month for a database of a few GB on Azure just feels like way too much.