Overview

This page starts with a summary of the problem then walks through some examples in Snowflake and SQL Server.

A quick note about downloads

For some reason, clicking the download links on this page doesn’t seem to download the files, but copying the links and pasting them into a new tab in Chrome does seem to work.

Problem Summary

There are two parts to this problem.

Problem Part 1

Power BI generates SQL that performs very poorly against Snowflake (and possibly other database platforms) when joining nullable columns, e.g. in the “Merge Queries” action in the Power BI query editor.

Specifically, the SQL generated prevents the use of the “hash join” physical join algorithm that has been one of the main algorithms used for decades by database engines when executing joins between large tables. This forces database engines to fall-back to using very inefficient physical joins (e.g. loop/cartesian join) that can perform orders of magnitude slower.

The reason why Power BI generates different SQL for non-nullable vs. nullable columns in a join is explained in the “Problem Part 2” section below.

Join Created By Power BI for non-nullable columns against Snowflake and SQL Server

X INNER JOIN Y
ON X.a = Y.b

Join Created By Power BI for nullable columns against Snowflake

X INNER JOIN Y
ON (X.a = Y.b and X.a IS NOT NULL and Y.b IS NOT NULL) OR (X.a IS NULL AND Y.b IS NULL)

Not only is this more convoluted than the SQL Server equivalent, it is particularly ugly SQL because Snowflake has a built-in function that expresses the intent much more clearly:

X INNER JOIN Y
ON EQUAL_NULL(X.a, Y.b)

Join Created By Power BI for nullable columns against SQL Server

X INNER JOIN Y
ON (X.a = Y.b) OR (X.a IS NULL AND Y.b IS NULL)

Full query examples are included later on this page.

Problem Part 2

On a more philosophical level, Power BI is matching null values just like any other value. This behaviour is very ill-advised in my opinion. NULL represents “no information”. It should not be treated as just another value, at least not without an option to control this behaviour.

For example, in a customer dimension, if there is a CustomerAlternateKey (as used in the example below), then it would be stupid to say all customers with a NULL CustomerAlternateKey are the same customer – and yet, in effect, because Power BI joins null values just like any other value, this is what it is doing. So both erroneous cross-matches and duplication will likely arise.

For example, consider the following simple customer and sales data:

If these sets of data were joined in SQL via inner join, the null values in the customer alternate key column and sale key column would not match each other, resulting in:

However, when these sets of data are joined via the “Merge Queries” inner join action in Power BI the resulting data is:

Note that Power BI has treated null like any other value and the join has duplicated sales. For analysts used to the behaviour of SQL, this is pretty wild behaviour.

A sample Power BI PBIX file containing the simple example above can be downloaded from here.

Scenario Background

We’ve recently moved to using Snowflake as our DW backend platform. It’s a fantastic modern machine, generally speaking able to easily process queries across large datasets – from performance, SQL-compatibility and cost perspectives. So we were puzzled when we saw a seemingly simple query generated by Power BI that was taking an extremely long time to execute (and on Snowflake, time=cost).

When I looked into the problem, I admit I was surprised at the cause of the problem and the rather naïve SQL generated by Power BI that seems to ignore how actual database engines in the real-world execute joins (specifically, they greatly prefer simple equality joins).

I’ve also recreated the scenario in both Snowflake and SQL Server using some sample data (based on AdventureWorksDW) for a couple of reasons:

• I can’t share the original code since it is part of our internal DW – so using sample data is a simple alternative.
• There are some commonalities and some differences in the behaviour of Power BI between the two platforms – but the behaviour in both is problematic.
• The query plans in SQL Server are a bit richer (or at least I am more familiar with them) so are a helpful way to investigate the problem.
• To show that this issue affects more than Snowflake (my hunch is most/all major DB platforms would be affected).
• So that this blog post can hopefully be used to establish a conversation with Microsoft on the issue.

Join Types Background

Before we get into the detail, I am conscious some people may not understand what we mean by logical vs. physical join types, so here is a really quick intro.

Logical Joins

When most people talk about join types, they talk about inner, left outer, right outer, full outer, etc. These are logical joins, i.e. they describe functionally how data rows from two tables should be joined together. What they don’t describe is exactly how the database engine will execute the join internally.

Physical Joins

Physical joins describe the algorithm that a database engine will use to perform a logical join. So for example, let’s take an inner join… there are actually several different algorithms that SQL Server (or any database platform) could use to actually do the join. The end results will be the same* whichever algorithm is chosen, but each possible algorithm that could be used has different pros and cons. Some work faster for smaller row counts, some need sorted data, some require more memory to execute than others, etc. It is the job of the query optimiser inside the database engine to choose which physical join algorithm to use based on expected row counts and other factors.

(* well, not exactly the same – the resulting order of the data may be different for example).

It is generally always best to let the database engine choose which join algorithm to use. It is however possible to use query hints to control some aspects of the physical join in SQL Server, which is what we will do on this page to explore this problem.

For more information on logical and physical join types, see here.

Example Data

The scenario is that, as an analyst, we have some alternative sales data that is not yet in the star schema but is in a separate table named AlternativeSales. This data however joins to the DimCustomer table from the star schema via the CustomerAlternateKey column that is present in both tables.

The AlternativeSales table contains around 3.8 million rows and the DimCustomer table contains around 18 thousand rows.

The key things to note are that the CustomerAlternateKey is nullable and a small percentage of the rows in both tables have NULL values in the CustomerAlternateKey column.

If you are interested in how the data was produced, it is detailed in the appendix at the bottom on this page.

But all key columns should be “NOT NULL” in a star schema…

This point is bound to come up at some point, so let’s get it out of the way here. In general I agree with this, but…

Whether we like it or not, the fact is Power BI is used in a range of scenarios today and analysts in the real-world are typically working with data from multiple/different sources – so in my view it’s just not credible to assume everyone will always be working only with well organised star schemas with well managed keys.

The data used in real-world analysis is often not just a simple star schema and analysts will often join data in ways that were not anticipated at design-time. (And we all know comparatively how long it takes to build star schemas, right?). I say all of this after 20 plus years of seeing how real-world teams work.

Power BI will always work best with star schemas, but I think it is wrong if very core Power BI functionality (like “Merge Queries”) only works in practice with pure star schemas.

Example Join

For discussion purposes, we will join the two tables to calculate the total sales amount by customer level of education (the field named EnglishEducation in DimCustomer). Writing this join in SQL, it would be:

SELECT c.EnglishEducation, SUM(s.SalesAmount) SalesAmount
FROM PbiJoinProblem.DimCustomer c
INNER JOIN PbiJoinProblem.AlternativeSales s
ON c.CustomerAlternateKey = s.CustomerAlternateKey
GROUP BY c.EnglishEducation
ORDER BY 1

Output:

Note that the customers and sales with NULL CustomerAlternateKey values have been eliminated.

Summary of Test Results

There are lots of different tests below. This section provides a quick reference summary of the test results below:

Power BI against Snowflake Test Results

With the exception of Tests 1b and 4, these tests were carried out in Power BI Desktop against Snowflake. Tests 1b and 4 were run in the Worksheets (query editor) in the Snowflake Snowsight web UI.

Snowflake SQL against SQL Server Test Results

These tests were taking the SQL generated in Tests 1 and 2 above, amending the syntax slightly to suit SQL Server rather than Snowflake (but not changing the join criteria) and then executing in SQL Server via SSMS.

Power BI against SQL Server Test Results

These tests were carried out in Power BI Desktop against SQL Server.

Key Findings of Tests

The SQL generated by Power BI against Snowflake (see Test 1) when joining nullable columns includes particularly convoluted join criteria. The join is more convoluted than the equivalent join generated by Power BI against SQL Server.

The SQL Server query optimiser also seems to have at least one join additional optimisation that Snowflake doesn’t have (compare the results of Snowflake Test 4 vs. Power BI against SQL Test 1) – however this only works because of the simpler join criteria generated by Power BI against SQL Server.

A great deal of this mess is caused by the fact Power BI insists on matching null values in joins in a way that SQL does not, i.e. null==null is TRUE in a Power BI join but FALSE in a SQL join.

What Changes are Needed in Power BI and Snowflake

Power BI

Change 1

An additional setting (checkbox) in Power BI Desktop in the “Merge Queries” function (i.e. an additional argument in the M Table.NestedJoin() function) that controls whether NULL values match in a join would be greatly beneficial. The checkbox could be named “Match NULL values” (default can be TRUE to match todays behaviour). If the checkbox was unticked, then the SQL generated should revert to the native SQL join types, e.g.

“Match NULL values” ticked (default):

X INNER JOIN Y 
ON (X.a = Y.b and X.a IS NOT NULL and Y.b IS NOT NULL) OR (X.a IS NULL AND Y.b IS NULL)

See “Change 2” below re: the superfluous SQL that is striked through above.

“Match NULL values” unticked:

X INNER JOIN Y
ON X.a = Y.b

This would solve both of the problems mentioned at the start of this page.

So as an example, we want to be able to generate this SQL against Snowflake:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on "OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY"
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

Instead of this more convoluted SQL:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on (("OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY" and not "OTBL"."CUSTOMERALTERNATEKEY" is null) and not "ITBL"."CUSTOMERALTERNATEKEY" is null or "OTBL"."CUSTOMERALTERNATEKEY" is null and "ITBL"."CUSTOMERALTERNATEKEY" is null)
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

Change 2

It would be better if Power BI could generate the same slightly simpler join SQL against Snowflake as it does against SQL Server (i.e. without the SQL striked through above). So even if the checkbox described above were checked to match null values in joins, the SQL generated by Power BI against Snowflake would look like:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on ("OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY") or ("OTBL"."CUSTOMERALTERNATEKEY" is null and "ITBL"."CUSTOMERALTERNATEKEY" is null)
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

Snowflake

If Power BI were able to generate the simpler SQL Server join SQL against Snowflake, then an optimisation is needed in Snowflake to allow HASH joins to be executed more efficiently for join criteria that looks like the join criteria immediately above, i.e.

X INNER JOIN Y 
ON (X.a = Y.b) OR (X.a IS NULL AND Y.b IS NULL)

This can equally be expressed in Snowflake as:

X INNER JOIN Y
ON EQUAL_NULL(X, Y)

e.g.

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on EQUAL_NULL("OTBL"."CUSTOMERALTERNATEKEY", "ITBL"."CUSTOMERALTERNATEKEY")
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

Power BI against Snowflake Tests

Snowflake Setup

If you wish to create this in your own Snowflake environment, the create table scripts are below:

CREATE TABLE S01_PBI_JOIN_PROBLEM.DimCustomer(
	CustomerKey int NOT NULL,
	GeographyKey int NULL,
	CustomerAlternateKey nvarchar(15) NULL,
	Title nvarchar(8) NULL,
	FirstName nvarchar(50) NULL,
	MiddleName nvarchar(50) NULL,
	LastName nvarchar(50) NULL,
	NameStyle boolean NULL,
	BirthDate date NULL,
	MaritalStatus nchar(1) NULL,
	Suffix nvarchar(10) NULL,
	Gender nvarchar(1) NULL,
	EmailAddress nvarchar(50) NULL,
	YearlyIncome number NULL,
	TotalChildren tinyint NULL,
	NumberChildrenAtHome tinyint NULL,
	EnglishEducation nvarchar(40) NULL,
	SpanishEducation nvarchar(40) NULL,
	FrenchEducation nvarchar(40) NULL,
	EnglishOccupation nvarchar(100) NULL,
	SpanishOccupation nvarchar(100) NULL,
	FrenchOccupation nvarchar(100) NULL,
	HouseOwnerFlag nchar(1) NULL,
	NumberCarsOwned tinyint NULL,
	AddressLine1 nvarchar(120) NULL,
	AddressLine2 nvarchar(120) NULL,
	Phone nvarchar(20) NULL,
	DateFirstPurchase date NULL,
	CommuteDistance nvarchar(15) NULL
);

CREATE TABLE S01_PBI_JOIN_PROBLEM.AlternativeSales(
	CustomerAlternateKey nvarchar(15) NULL,
	ProductKey int NOT NULL,
	OrderDateKey int NOT NULL,
	CurrencyKey int NOT NULL,
	SalesOrderNumber nvarchar(20) NOT NULL,
	SalesOrderLineNumber tinyint NOT NULL,
	SalesAmount number(38, 12) NOT NULL
);

The tables can be loaded using:

copy into S01_PBI_JOIN_PROBLEM.DimCustomer
  from @LOADING.S3_STAGE/01-pbi-joining-problem/DimCustomer.txt.gz
  FILE_FORMAT = ( FORMAT_NAME = 'LOADING.GZIP_TSV');
  
copy into S01_PBI_JOIN_PROBLEM.AlternativeSales
  from @LOADING.S3_STAGE/01-pbi-joining-problem/AlternativeSales.txt.gz
  FILE_FORMAT = ( FORMAT_NAME = 'LOADING.GZIP_TSV');

The file format is defined as:

CREATE OR REPLACE FILE FORMAT LOADING.GZIP_TSV
    TYPE = CSV
    COMPRESSION = GZIP
    FIELD_DELIMITER = '\t'
    SKIP_HEADER = 1
    BINARY_FORMAT = BASE64;

The two data files used can be downloaded from:

http://misc.cbailiss.me.uk/pbijoin/DimCustomer.txt.gz
http://misc.cbailiss.me.uk/pbijoin/AlternativeSales.txt.gz

Test 1 – Joining Nullable Columns

We build two queries in the Power BI Query Builder and join them using “Merge Queries”, specifying the join type as “inner join”.

Query dependencies:

DimCustomer query:

AlternativeSales query (very slow to execute):

Both queries are disabled – neither is set to load into the data model as everything we need to do can be done in the query designer. We are only interested in the AlternativeSales query. This query takes an incredible 9 min 38 sec to execute.

The Power BI PBIX can be downloaded from here.

The results eventually are:

The numbers are hard to read above, but the level of double counting is clear. Instead of 538 million for the first number we now have 222 billion due to the Power BI null matching behaviour.

The SQL generated by Power BI is:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on (("OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY" and not "OTBL"."CUSTOMERALTERNATEKEY" is null) and not "ITBL"."CUSTOMERALTERNATEKEY" is null or "OTBL"."CUSTOMERALTERNATEKEY" is null and "ITBL"."CUSTOMERALTERNATEKEY" is null)
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

Note the convoluted join condition in the SQL above:

inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on (("OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY" and not "OTBL"."CUSTOMERALTERNATEKEY" is null) and not "ITBL"."CUSTOMERALTERNATEKEY" is null or "OTBL"."CUSTOMERALTERNATEKEY" is null and "ITBL"."CUSTOMERALTERNATEKEY" is null)

This forces what Snowflake calls a cartesian join in the query plan (which I think is essentially the same as a loop join in SQL Server):

Look at the incredible number of rows (1.5 billion) coming out of the cartesian join!

As a comparison, we can take the SQL generated by Power BI and simplify the join back to a simple inner join. We add in GETDATE() as a simple way of ensuring the query executes and the results do not come from the Snowflake results cache:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1",
    GETDATE()
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on "OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY"
)
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0;

As expected, this query executes in less than a second, produces the expected values and the query plan looks more sensible:

Test 2 – Joining Filtered Nullable Columns

A variation of the above test was carried out where the two queries in Power BI each had a step added that filtered out NULL CustomerAlternateKey values prior to the “Merge Queries” step.

The Power BI PBIX can be downloaded from here

This query generated the expected results (i.e. not double counting):

Power BI generated the following SQL for Snowflake:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from 
    (
        select "CUSTOMERALTERNATEKEY",
            "SALESAMOUNT"
        from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES"
        where not "CUSTOMERALTERNATEKEY" is null
    ) as "OTBL"
    inner join 
    (
        select "CUSTOMERALTERNATEKEY",
            "ENGLISHEDUCATION"
        from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER"
        where not "CUSTOMERALTERNATEKEY" is null
    ) as "ITBL" on (("OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY" and not "OTBL"."CUSTOMERALTERNATEKEY" is null) and not "ITBL"."CUSTOMERALTERNATEKEY" is null or "OTBL"."CUSTOMERALTERNATEKEY" is null and "ITBL"."CUSTOMERALTERNATEKEY" is null)
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

Note the additional sub-queries in the SQL, each containing a WHERE clause to eliminate null values.

This resulted in a query that was a little quicker to execute (5 min 37 secs) but was still ultimately very slow compared to the optimum query. Even though the null values have been eliminated, Snowflake still executed the query with a cartesian join.

Test 3 – Joining Nullable Columns Containing No Null Values

As a quick final test, I deleted the rows from each table containing NULL CustomerAlternateKey values and then executed the original queries from the PBIX file used in Test 1. In this case, the query executed in a couple of seconds. Either Power BI recognised there were no null values present and generated the simpler SQL or Snowflake used data statistics to recognise no null values are present.

While this is an interesting result, it is not really relevant to the main problem – which is how Power BI joins two tables when null values are present.

Back to the Real World

In our internal DW, the tables that we were joining were significantly bigger (20 million rows in each) and the cartesian join resulted in a Snowflake query execution time that was predicted to be four days (the query was stopped after 1 hour). As an interesting aside, at standard Snowflake pay-as-you-go rates, that query would have cost $480 and produced meaningless junk.

As a comparison, when the simple inner join query was executed with our internal DW data, Snowflake was able to complete the query (joining 20 million rows) in a couple of seconds. 4 days vs. a couple of seconds!

SQL Server SQL Tests

To look at the problem from a different perspective, we will now investigate it in SQL Server.

We will approach this in two parts. Firstly, we will execute the SQL we captured above in SSMS against SQL Server, to see the join algorithms that SQL Server is able to use. Then we will build the same test cases in Power BI Desktop that we used against Snowflake above to see how Power BI generates the equivalent SQL for SQL Server.

SQL Server Setup

A SQL Server 2019 database backup file containing test data can be downloaded from here.

Snowflake SQL against SQL Server Test Results

Test 1 – Joining Nullable Columns

The SQL from Snowflake Test 1 above written in SQL Server syntax is:

select TOP 1000 ENGLISHEDUCATION,
    sum({ fn convert(SALESAMOUNT, SQL_DOUBLE) }) as C1
from 
(
    select OTBL.CUSTOMERALTERNATEKEY,
        OTBL.SALESAMOUNT,
        ITBL.ENGLISHEDUCATION
    from PbiJoinProblem.ALTERNATIVESALES as OTBL
    inner join PbiJoinProblem.DIMCUSTOMER as ITBL on ((OTBL.CUSTOMERALTERNATEKEY = ITBL.CUSTOMERALTERNATEKEY and not OTBL.CUSTOMERALTERNATEKEY is null) and not ITBL.CUSTOMERALTERNATEKEY is null or OTBL.CUSTOMERALTERNATEKEY is null and ITBL.CUSTOMERALTERNATEKEY is null)
) as ITBL
group by ENGLISHEDUCATION
order by ENGLISHEDUCATION

The query results include the double counting:

The query executed in 1 min 3 secs using a loop join physical join and possibly (if I am interpreting the query correctly) a hash match to pre-aggregate the data prior to joining – which for this set of data (lots of duplicate rows) works well to reduce the number of rows that need processing by the loop join – but would perhaps be of limited value with real data:

We can execute the simple inner loop version from Test 1:

select TOP 1000 ENGLISHEDUCATION,
    sum({ fn convert(SALESAMOUNT, SQL_DOUBLE) }) as C1
from 
(
    select OTBL.CUSTOMERALTERNATEKEY,
        OTBL.SALESAMOUNT,
        ITBL.ENGLISHEDUCATION
    from PbiJoinProblem.ALTERNATIVESALES as OTBL
    inner join PbiJoinProblem.DIMCUSTOMER as ITBL on OTBL.CUSTOMERALTERNATEKEY = ITBL.CUSTOMERALTERNATEKEY
) as ITBL
group by ENGLISHEDUCATION
order by ENGLISHEDUCATION

This executes in 1 sec and produces the expected results (no double counting):

The query plan included the hash join physical join:

If you try and force a hash join in the first query above by adding the HASH hint, an error results because this join criteria cannot be processed by a hash join:

select TOP 1000 ENGLISHEDUCATION,
    sum({ fn convert(SALESAMOUNT, SQL_DOUBLE) }) as C1
from 
(
    select OTBL.CUSTOMERALTERNATEKEY,
        OTBL.SALESAMOUNT,
        ITBL.ENGLISHEDUCATION
    from PbiJoinProblem.ALTERNATIVESALES as OTBL
    inner HASH join PbiJoinProblem.DIMCUSTOMER as ITBL on ((OTBL.CUSTOMERALTERNATEKEY = ITBL.CUSTOMERALTERNATEKEY and not OTBL.CUSTOMERALTERNATEKEY is null) and not ITBL.CUSTOMERALTERNATEKEY is null or OTBL.CUSTOMERALTERNATEKEY is null and ITBL.CUSTOMERALTERNATEKEY is null)
) as ITBL
group by ENGLISHEDUCATION
order by ENGLISHEDUCATION

Error:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

This is the essence of the performance problem caused by the SQL generated by Power BI when joining nullable columns – the most common/efficient physical join algorithm is unable to process the join criteria.

Test 2 – Joining Filtered Nullable Columns

The SQL from Snowflake Test 2 above written in SQL Server syntax is:

select TOP 1000 ENGLISHEDUCATION,
    sum({ fn convert(SALESAMOUNT, SQL_DOUBLE) }) as C1
from 
(
    select OTBL.CUSTOMERALTERNATEKEY,
        OTBL.SALESAMOUNT,
        ITBL.ENGLISHEDUCATION
    from 
    (
        select CUSTOMERALTERNATEKEY,
            SALESAMOUNT
        from PbiJoinProblem.ALTERNATIVESALES
        where not CUSTOMERALTERNATEKEY is null
    ) as OTBL
    inner join 
    (
        select CUSTOMERALTERNATEKEY,
            ENGLISHEDUCATION
        from PbiJoinProblem.DIMCUSTOMER
        where not CUSTOMERALTERNATEKEY is null
    ) as ITBL on ((OTBL.CUSTOMERALTERNATEKEY = ITBL.CUSTOMERALTERNATEKEY and not OTBL.CUSTOMERALTERNATEKEY is null) and not ITBL.CUSTOMERALTERNATEKEY is null or OTBL.CUSTOMERALTERNATEKEY is null and ITBL.CUSTOMERALTERNATEKEY is null)
) as ITBL
group by ENGLISHEDUCATION
order by ENGLISHEDUCATION

The query results correctly do not include the double counting:

The query executes in around 1 sec, i.e. SQL Server is able to use the more optimal HASH join algorithm:

Test 3 – Joining Nullable Columns Containing No Null Values

If we delete the rows containing null CustomerAlternateKey values in both tables, and try executing the first SQL query for SQL Server Test 1 above, we get the same SQL Server behaviour – i.e. SQL Server appears not to use statistics to determine whether null values actually exist in order to use a HASH join.

Power BI against SQL Server Tests

Power BI often does not generate the same SQL against different data sources. So, let’s build the same queries in Power BI Desktop against SQL, including the “Merge Queries” action and look at the SQL that is generated.

Test 1 – Joining Nullable Columns

Attempting to build the queries in Power BI Desktop is painful. When adding the “Merge Queries” step, the Query Builder gets stuck reading data incredibly slowly:

It appears to be attempting to perform the join in Power BI instead of using SQL Server to perform the join.

For the PBIX file at this point, see SQL Server Example 1 from here.

To work around this problem I temporarily added a “Keep Top 1000” step before the “Merge Queries” step. This allowed me to build the rest of the query. I could then remove the “Keep Top 1000” step however if I accidentally clicked on the “Merged Queries” step, Power BI Desktop would again try to download all of the data (this is painful to work with!).

For the PBIX file at this point, see SQL Server Example 2 from here.

The final query was now able to execute:

As we might assume by now, the results include the double counting:

The SQL generated was:

select top 1000
    [_].[EnglishEducation],
    [_].[SalesAmount]
from 
(
    select [rows].[EnglishEducation] as [EnglishEducation],
        sum([rows].[SalesAmount]) as [SalesAmount]
    from 
    (
        select [$Outer].[CustomerAlternateKey],
            [$Outer].[SalesAmount],
            [$Inner].[EnglishEducation]
        from 
        (
            select [CustomerAlternateKey],
                [SalesAmount]
            from [PbiJoinProblem].[AlternativeSales] as [$Table]
        ) as [$Outer]
        inner join 
        (
            select [CustomerAlternateKey] as [CustomerAlternateKey2],
                [EnglishEducation] as [EnglishEducation]
            from [PbiJoinProblem].[DimCustomer] as [$Table]
        ) as [$Inner] on ([$Outer].[CustomerAlternateKey] = [$Inner].[CustomerAlternateKey2] or [$Outer].[CustomerAlternateKey] is null and [$Inner].[CustomerAlternateKey2] is null)
    ) as [rows]
    group by [EnglishEducation]
) as [_]
order by [_].[EnglishEducation]

Interestingly, even given the slightly non-trivial join condition, the query executed in only a couple of seconds. The query plan was:

The above is interesting, it shows that Power BI is generating a simple SQL query for SQL Server than it does for Snowflake. This query can fully execute using the HASH join physical join. We will test this version of the query against Snowflake later further below on this page (see Test 4 section further below).

Test 2 – Joining Filtered Nullable Columns

For completeness, we’ll filter the two tables in Power BI to exclude NULL CustomerAlternateKey values before the merge join.

For the PBIX file at this point, see SQL Server Example 3 from here.

The results of this in Power BI, as expected, do not include the double counting:

The SQL generated by Power BI was:

select top 1000
    [_].[EnglishEducation],
    [_].[SalesAmount]
from 
(
    select [rows].[EnglishEducation] as [EnglishEducation],
        sum([rows].[SalesAmount]) as [SalesAmount]
    from 
    (
        select [$Outer].[CustomerAlternateKey],
            [$Outer].[SalesAmount],
            [$Inner].[EnglishEducation]
        from 
        (
            select [_].[CustomerAlternateKey],
                [_].[SalesAmount]
            from 
            (
                select [CustomerAlternateKey],
                    [SalesAmount]
                from [PbiJoinProblem].[AlternativeSales] as [$Table]
            ) as [_]
            where [_].[CustomerAlternateKey] is not null
        ) as [$Outer]
        inner join 
        (
            select [_].[CustomerAlternateKey] as [CustomerAlternateKey2],
                [_].[EnglishEducation] as [EnglishEducation]
            from 
            (
                select [CustomerAlternateKey],
                    [EnglishEducation]
                from [PbiJoinProblem].[DimCustomer] as [$Table]
            ) as [_]
            where [_].[CustomerAlternateKey] is not null
        ) as [$Inner] on ([$Outer].[CustomerAlternateKey] = [$Inner].[CustomerAlternateKey2] or [$Outer].[CustomerAlternateKey] is null and [$Inner].[CustomerAlternateKey2] is null)
    ) as [rows]
    group by [EnglishEducation]
) as [_]
order by [_].[EnglishEducation]

This features IS NOT NULL WHERE clauses as expected and still features the simpler join criteria than was generated against Snowflake. The query executed in around 5 seconds using the following query plan:

Revisiting Snowflake

Test 4 – Power BI SQL Server Query executed against Snowflake

We take the Test 1 query generated by Power BI against SQL Server and test executing this against Snowflake. The SQL is:

select "ENGLISHEDUCATION",
    sum({ fn convert("SALESAMOUNT", SQL_DOUBLE) }) as "C1"
from 
(
    select "OTBL"."CUSTOMERALTERNATEKEY",
        "OTBL"."SALESAMOUNT",
        "ITBL"."ENGLISHEDUCATION"
    from "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."ALTERNATIVESALES" as "OTBL"
    inner join "SBOX_CBAILISS_GENERAL"."S01_PBI_JOIN_PROBLEM"."DIMCUSTOMER" as "ITBL" on (("OTBL"."CUSTOMERALTERNATEKEY" = "ITBL"."CUSTOMERALTERNATEKEY") or "OTBL"."CUSTOMERALTERNATEKEY" is null and "ITBL"."CUSTOMERALTERNATEKEY" is null)
) as "ITBL"
group by "ENGLISHEDUCATION"
order by "ENGLISHEDUCATION"
LIMIT 1000 OFFSET 0

As you might assume, the results include the double counting:

The query execution time was significantly improved at 54 seconds (instead of 9 minutes plus), but this is still much slower than the simple join execution time in Snowflake (less than 1 second). The query plan doesn’t feature the cartesian join but still features an ugly large number of rows after the join:

Appendix: SQL Server Data Generation Process

Download and Restore AdventureWorksDW

Download the AdventureWorksDW database from here. Note – ensure you download the DW version, not the OLTP version. For the record, I ran the code on this page using SQL Server 2019.

Restore the database into your SQL Server.

Prepare AdventureWorksDW

The following script created the test tables/data:

CREATE SCHEMA PbiJoinProblem;

CREATE TABLE PbiJoinProblem.[DimCustomer](
	[CustomerKey] [int] NOT NULL,
	[GeographyKey] [int] NULL,
	[CustomerAlternateKey] [nvarchar](15) NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[NameStyle] [bit] NULL,
	[BirthDate] [date] NULL,
	[MaritalStatus] [nchar](1) NULL,
	[Suffix] [nvarchar](10) NULL,
	[Gender] [nvarchar](1) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) NULL,
	[SpanishEducation] [nvarchar](40) NULL,
	[FrenchEducation] [nvarchar](40) NULL,
	[EnglishOccupation] [nvarchar](100) NULL,
	[SpanishOccupation] [nvarchar](100) NULL,
	[FrenchOccupation] [nvarchar](100) NULL,
	[HouseOwnerFlag] [nchar](1) NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[AddressLine1] [nvarchar](120) NULL,
	[AddressLine2] [nvarchar](120) NULL,
	[Phone] [nvarchar](20) NULL,
	[DateFirstPurchase] [date] NULL,
	[CommuteDistance] [nvarchar](15) NULL
);

INSERT INTO PbiJoinProblem.DimCustomer
SELECT [CustomerKey]
      ,[GeographyKey]
      ,CASE WHEN ABS(CHECKSUM(NewId())) % 14 > 11 THEN NULL ELSE [CustomerAlternateKey] END
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[NameStyle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Suffix]
      ,[Gender]
      ,[EmailAddress]
      ,[YearlyIncome]
      ,[TotalChildren]
      ,[NumberChildrenAtHome]
      ,[EnglishEducation]
      ,[SpanishEducation]
      ,[FrenchEducation]
      ,[EnglishOccupation]
      ,[SpanishOccupation]
      ,[FrenchOccupation]
      ,[HouseOwnerFlag]
      ,[NumberCarsOwned]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[Phone]
      ,[DateFirstPurchase]
      ,[CommuteDistance]
FROM dbo.DimCustomer;

CREATE TABLE PbiJoinProblem.AlternativeSales
(
	[CustomerAlternateKey] [nvarchar](15) NULL,
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[SalesAmount] [money] NOT NULL
);

ALTER TABLE PbiJoinProblem.AlternativeSales REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

INSERT INTO PbiJoinProblem.AlternativeSales
SELECT dc.CustomerAlternateKey, f.ProductKey, f.OrderDateKey, f.CurrencyKey, 
	substring(CAST(newid() as varchar(50)),1,20), f.SalesOrderLineNumber, f.SalesAmount
FROM dbo.FactInternetSales f
INNER JOIN PbiJoinProblem.DimCustomer dc
ON f.CustomerKey = dc.CustomerKey;

-- run the following repeatedly (5-6 times to generate a few million rows in AlternativeSales table)
INSERT INTO PbiJoinProblem.AlternativeSales
SELECT CustomerAlternateKey, ProductKey, OrderDateKey, CurrencyKey, 
	substring(CAST(newid() as varchar(50)),1,20), SalesOrderLineNumber, SalesAmount
FROM PbiJoinProblem.AlternativeSales

All tables other than the two tables created above were then dropped from the database.

Power BI has native connectivity to  Azure SQL DW, including via Direct Query, allowing interactive reports to be run over extremely large data volumes.

One niggle is the inherent limitation in Power BI of only being able to create one relationship between each table.  For one of my recent use cases with Azure SQL DW, this Power BI limitation meant it wasn’t possible to have Power BI generate queries that are optimum.

Generally speaking with Azure SQL DW, eliminating/minimising the data movement between the compute nodes in query plans is a good thing.  In my scenario, I could easily hand write a SQL query that minimised data movement by including an extra join condition.  Power BI can’t generate this extra condition however, so that plan isn’t so optimised.

All the details are here:

https://community.powerbi.com/t5/Integrations-with-Files-and/Does-Power-BI-fully-support-Azure-SQL-Data-Warehouse/m-p/90435

It’s a modern example of the age-old problem that handwritten code can usually be written that outperforms machine written code.  We have all shiny new technology, and incredible technology it certainly is, but often the age-old niggles are still there…

UPDATE  14th May 2018:  The Power BI team has added the COMBINEVALUES() function, which helps greatly with the above problem.  Details already blogged at:

https://www.sqlbi.com/articles/using-combinevalues-to-optimize-directquery-performance

• Write performance is doubled for all service tiers
• Read performance is doubled for the Premium service tiers.

The official announcement is here.

For the benefit of anyone reading the older posts below, please remember some of the numbers should (in theory) be doubled.

Therefore, I have done a few quick retests.  Results:

• S0 write:  24 MB per minute = 0.4 MB per second.
• S1 write:  36 MB per minute = 0.6 MB per second.
• S2 write:  48 MB per minute = 0.8 MB per second.

This shows Microsoft have adjusted the performance of S0 to S2 downwards from the performance in Jan 2015.  This is not surprising, since the results in Jan 2015 showed S0 and S1 had roughly equal performance, with S2 significantly more (even higher than S3).

I also ran the memory tests again.  The results reasonably closely matched my earlier tests (i.e. S0 = 500 MB, S1 = 990 MB, S2 = 2.2 GB).

It is worth noting that some blog posts have appeared over the past few months (e.g. here) that talk about using dm_os_performance_counters to obtain memory information.  For standard tier at least, I don’t believe these numbers are what they appear to be.  For example, target server memory using this DMV for both S1 and S2 is 3GB.  The dm_db_resource_stats DMV numbers suggest it is impossible to utilise 3 GB of memory on either an S1 or S2.

(All methodologies in these retests were the same as in the earlier tests).

Version 12 of Azure SQL Database has now reached General Availability in Europe.  See the announcement here.  So, now it is time to re-run those earlier performance tests, to see where performance has settled at.

This post includes:

• updated read / write metrics
• a new CPU benchmark and
• updated memory allocation metrics.

Microsoft publish very few absolute performance metrics regarding Azure SQL Database.  The tests described here involve indirectly calculating absolute metrics.  Methodologies are described below.

All tests were conducted at least three times to check for consistency and to reduce the risk of any random variations affecting the results.  (That is a lot of tests, data collection and processing – nearly 80 test runs in total).

For reference, these tests were conducted in the Western Europe data centre, against the following Azure SQL Database version (select @@version):

• Microsoft SQL Azure (RTM) – 12.0.2000.8   Jan 29 2015 07:51:58

Unit of Measure

Note that the unit of measure for throughput rates in this post (and the other posts in this series) are MB per minute.  This is primarily because, at the lower tiers (and even the higher tiers until recently), rates of MB per second would be 0.xx, 1.xx, etc.  As the throughput rates have been increased, particularly recently, this has led to the higher performance level rates entering the thousands, so we have the opposite problem (i.e. too large numbers at the top end).  Still, despite that, I have opted to retain the same unit of measure throughout, to keep comparisons easier.  In the future, I may move to MB per second, if I get a lot of feedback in that direction.

Read Rate Tests

The test is based around reading data from a series of identical tables.  The tables contain a mixture of basic data types – no large object data types, all in-row data.  Average row size is around 410 bytes.

The test involved using a set of test data that is likely much larger than the SQL Buffer Pool sizes:

• Basic: 1.9 GB
• Standard S0:  10 GB
• Standard S1:  20 GB
• Standard S2:  30 GB
• Standard S3:  36 GB
• Premium P1 and P2:  36 GB

A new database was created in each service level and populated to the sizes given above.  The database was then copied (using the Database Copy facility).  My understanding is that this performs a physical copy of the database files (determined from some other tests I have undertaken previously) and results in a new database which has an empty buffer pool i.e. no data cached in memory.  The test then involves sequentially reading through the database reading data from disk allowing the maximum physical disk read rate to be measured.

Data was divided into 1 GB tables (100 MB tables for Basic Tier) and each table was read sequentially.

The SQL statements used to read through the database were similar to the following:

select count(*) from Table0
where [Bit1] is not null and
[TinyInt1] between 10 and 240 and
[Int3] between 100000 and 2000000000 and
[Money2] between 0 and 10000000

No indexes (other than a clustered index on the RowId primary key) were present on the tables.  A count(*) was used to ensure performance is not held back either by complex processing or by waiting for slow network I/O returning results to the client.

Results

The blue bars show performance values obtained in my first tests in July 2014 during the preview of the newer service tiers (Basic, Standard and Premium).  The red bars show performance in the current generally available version (i.e. V11) of SQL Database.  V12 preview performance, measured in December 2014, is shown in the green bars.  The orange bars show V12 GA performance (except for Web/Business, which only exists at v11 – the orange Web/Business bar shows the result of a v11 test conducted in January 2015).

Premium P1 and P2 were also tested:

Several interesting observations here.  Most notably, Premium P1 and P2 have received very significant read performance increases.  Premium performance now makes Web/Business performance appear tiny in comparison.  With this performance we can perhaps now understand why Microsoft set the Premium Tier prices so expensively in comparison to Web/Business.  No doubt, last summer when the Premium Tier pricing was announced, it was with this performance level in mind.  I expect Microsoft faced an interesting dilemma last year, do they pitch the initial Premium tier price lower in July 2014 (in line with the initial lower performance) and then dramatically increase it in January 2015 as this v12 upgrade comes on-line (and annoy customers in Jan 2015), or do they set it higher from the start (at the risk of making Premium look very poor value initially).  Obviously they chose the latter, and now the full story is becoming clearer.

This is a good point to note that at the beginning of these tests, the SQL Server Buffer Pool was empty (verified by checking the memory usage column in the sys.dm_db_resource_states DMV).  In addition, five to six hours had also elapsed between the creation of the database copies and the running of the tests, which should reduce potential effects from storage layer caching (though there is no way to verify this).  Thus the tests should be measuring the read rate from disk, not cache.

Standard Tier v12 GA performance for S1, S2 and S3 is significantly less than in preview.  However, it would not be fair to criticise Microsoft for any performance changes here.  It was quite clearly stated during the preview that performance could/would be adjusted as the service heads towards general availability.

More important is the comparison between current v11 performance and v12 GA performance.  Here we can see that maximum read rates under S0 and S1 have more than doubled.  S0 and S2 have received a slight increase.  Curiously Basic tier seems to have received a slight decrease.

It is particularly interesting to see where S2 and S3 read performance now sits.  S2 runs at almost the exact average of Web/Business.  Current Web/Business read performance is on average around 550 MB per minute though is quite volatile flipping between 200 MB per minute and 800 MB per minute (see my previous post here for more details).

S3 runs at on average 900 MB per minute – i.e. well above the average Web/Business performance and above even the Web/Business peak rates.

There is a wide gap between S3 performance and P1 performance.  Both S3 and P1 are rated at 100 DTUs, though P1 costs five times as much as S3.  Clearly, for that price difference you get a massive amount of extra read-performance.  This suggests that the DTU figures do not take into account I/O performance limits/variations between tiers / performance levels.

Read Rate Consistency

The following charts show how the read rates varied over the duration of the tests.  Each read test was performance three times at each performance level – labelled as (a), (b) and (c) in the charts:

A point is plotted on the chart for each table read during the test.  For Basic tier, each table contained 100 MB (showing first  1GB only on the chart).  For the other service tiers, each table contained 1 GB.

The chart shows that read rates were generally very consistent throughout.  Continuing with the other service tiers:

S1 read performance was generally consistent, S2 read performance varied a little but there does seem to be some quite wide variation of S3 read performance.  For P1 and P2:

Again, both P1 and P2 are reasonably consistent in their performance.  The P2a test run was a little more variable than the other two P2 tests.  S3 is shown on this chart to illustrate just how much higher Premium performance is than S3.  On this chart, the scale is such that the relatively wider variation in S3 performance (shown on the previous chart) is barely even visible here.

Read Test Resource Usage

During the read tests, the data from the sys.dm_db_resource_stats DMV was captured.  This showed that, in the S0, S1 and S2 tests, around 90% of the available read IO resources were being consumed, so these results should be a good reflection of the maximum rates available.

The S3 result was a little more volatile, showing that at times significantly less than 100% of the available read resources were being consumed.

As you may expect, the drops in read utilisation are generally speaking at the same points in the test as the drops in read rate occur (e.g. for at 8 GB in S3b and 22 GB in S3c).  This suggests the I/O subsystem was unable to supply the maximum amount of data per second that SQL Server could consume in this performance level.  All in all this suggests that Microsoft still need to apply some performance optimisations for S3.

Interestingly, the chart for P2 also shows that it was not consuming all of the theoretically available read resources:

This chart has less detail than previous charts.  This is because P2 read the test database very quickly, and there is only one row in the sys.dm_db_resource_stats DMV for every fifteen seconds.  Even still, the general trend is still clear enough.  The test query was consuming only around 70% of the available read resources during the test.

Knowing that the P2 test read on average at 19,500 MB per minute at 70% read utilisation, we can estimate that the theoretical P2 maximum read rate would be around 28 GB per minute.  Similarly, we can estimate that the maximum theoretical read rate for P1 (which was reading 11,800 MB per minute on average at 80% read utilisation) would be around 15 GB per minute.

More Read Utilisation charts for the other performance levels can be found in an appendix at the end of this article.

Write Rate Tests

This test aimed to cache a relatively small amount of data in the buffer pool (100 MB), then repeatedly write this until a specified total data volume had been written.  Each 100 MB chunk was written using a single Insert statement.  The total data volume was specified to aim for a test very roughly around 30 minutes to 1 hour in length  The time taken would then allow an estimate of the maximum write rate.  Total data volumes used were:

• Basic and S0 – 1 GB.
• S1, S2 and S3 – 2 GB.
• P1 – 5 GB.
• P2 – 10 GB.

Results

Both Basic Tier and all four performance levels within Standard Tier have received notable write rate increases.

However the first question here is this: why is S3 write performance notably less than S2 write performance?  Tests at both S2 and S3 were repeated three times each – the results were consistent in all cases.  This seems very strange and I have put this question to Microsoft.

Edit:  I have received a response from Microsoft that the current write rates for the Standard Tiers are in v12 are about to be adjusted.  Once this has been completed I will update the details here.

It is interesting to note however, that none of the performance levels in Standard Tier come close to matching the Web/Business write performance.  Having said that, if S3 write performance was actually above S2 (as surely it is supposed to be) than S3 may actually come close to Web/Business.  I can see this being a key question for many existing Web/Business customers.

Moving on to write performance of the Premium Tier:

P1 and P2 see significant write performance increases – a factor of almost three over current v11.  P1 write performance is now clearly higher than Web/Business – which is not the case in v11.  v12 P1 writes also outperform v11 P2.

Write Rate Consistency

The following charts show how the write rates varied over the duration of the tests.  Each write test was performance three times at each performance level – labelled as (a), (b) and (c) in the following charts.

This data shows Basic, S0 and S1 writing very consistently at around 45 MB per minute throughout.  These charts are included in an appendix at the end of this post (since they all write at around 45 MB per minute, each performance level must be plotted on separate charts to avoid all the lines overlapping – which results in just too many charts to include in the main body of the text).

Comparing S1, S2, S3 and P1:

This chart shows how the write rates for S1, S2, S3 and P1 are very consistent in all tests.

This chart also shows how very consistently S2 outperforms S3.  Surely there is a bug here in Azure?

Now comparing P1 and P2:

Again, the write rate is extremely consistent for P2.

Write Test Resource Usage

During the write tests, the data from the sys.dm_db_resource_stats DMV was captured.  Unsurprisingly given the consistency shown above, this data shows the write-utilisation at very close to 100% (i.e. 98%, 99%), throughout.  As such, there is very little point plotting the data.

CPU Tests

In previous series of tests I have not compared CPU performance between the different performance levels.  As part of this series of tests, I decided running some tests to compare CPU performance between the different performance levels might be interesting.

The test is based around running the same CPU-heavy workload in each of the different performance levels and comparing the results.  The chosen workload is a forced loop join where the join condition is something non-trivial, for these tests:

(abs(((t1.Int1 * t2.Int2) + t1.Int3) - ((t2.Int1 * t1.Int2) + t2.Int4)) < 17) and (abs((power(t1.Int5 % 5, 3) - (t2.Int5 % 25))) < 7)

This query means that a relatively small number of rows will require a significant amount of time to process.  Using a relatively small number of rows is important because it eliminates any potential delays due to time waiting for the data to be read.  The test table was created with 280,000 rows of data which is in total around 10 MB.  This entire table was read before the test began properly to get the data loaded into the buffer pool.  All in all this means close to all of the query execution time is CPU time.

The query was written so that SQL Server should use a parallelised execution plan.  This allows the workload to expand to exploit the full amount of CPU resources available within the performance level.  The exact same data was used for every test.

The test script used automatically ran the test multiple times, recording the results for each test.  The number of rows selected from the test table was increased with each test (SELECT TOP … ORDER BY RowId was used to ensure data is picked up in the same way in each performance level).  The number of rows was increased until the SELECT ran for over two minutes, at which time no further tests were peformed (i.e. the row count wasn’t increased further).

The test was developed on my development machine.  This has the added advantage that the Azure results can be compared to a machine of known specification.  My development machine was built in 2010 – at the time it was a relatively high end machine.  At the core of the machine is an Intel i7 930 2.8 GHz quad core processor with hyperthreading (i.e. eight logical cores) running on an ASUS P6T Deluxe motherboard.  Details of the processor can be found here.  Details of the motherboard here.  My development machine is running an up to date copy of SQL Server 2014 so it is assumed that the engine internals involved in the test are close enough to those running in Azure SQL Database to make a comparison valid.

Results

Given the relatively large number of test cases, each with different row counts, it is easier to display these results in tabular form:

The table above shows three metrics, explained below.

Average query time in seconds

This shows the time taken to execute the test query in each performance level – row count combination.  For example, in the S0 column we can see that 34 seconds were required to perform the join, where the first 3000 rows were selected from the table.  The Ref column refers to my development machine (i.e. the reference machine).

In case anyone is jumping to the wrong idea… as I said above, the test query used was deliberately chosen to take a relatively long time to execute.  This query time is not indicative of the time taken to join 3000 rows in a typical business scenario!  In fact, the actual execution time is not really meaningful since the query is junk – all we care about is that it is a relatively CPU heavy workload that we can repeatedly execute in different environments and compare the execution times.

It is by comparing results between different environments that more meaningful insights can be gained.

When interpreting this table, it is important to understand what are valid/simple comparisons and what aren’t.  Comparing along the rows is valid e.g. comparing the the time to join 7500 rows on an S0 and an S1.  Joining the first 7500 rows from the test table on an S2 required 49 seconds.  Performing exactly the same join across the same test data on an S3 required 24 seconds.  Since it was the same workload, we can say S3 is twice as powerful in CPU terms as S2.

Comparing between rows in this table is not so simple.  I.e. this workload does not scale linearly.  E.g. on an S2, joining 5000 rows required 22 seconds.  Joining 10000 rows required 86 seconds.  As we are talking about a loop join here, double the rows equals four times the workload, which the results roughly agree with.

Finally, very short query times are unlikely to be good sources of information.  It is possible in much shorter queries that SQL Server may use a different execution plan.  It is also possible a larger percentage of the time may be attributable to non-CPU sources.  This is roughly those results from joining 2000 rows or less, which is why these rows have been grayed out in the table.

Performance Multiple of Reference System (Quad Core 2.8 GHz i7 930)

This metric is shown in the middle part of the table.  If we assign my development machine a metric value of 1, then comparing the query times between this system and the different Azure performance levels, we can see what fraction of my development machine CPU resources those performance levels offer.  For example, if a (CPU-heavy) query runs in ten seconds on my machine and takes twenty seconds in a particular performance level in Azure, we can say that this performance level in Azure offers half the CPU resources of my development machine.

The numbers highlighted in yellow show these results.  These show that, for example, S3 offers about one-fifth of the CPU resources of my dedicated development machine.

DTU Multiple

This metric is shown in the latter part of the table.  Taking S2 at 50 DTUs as a reference point and using the performance multiples above, it is possible to calculate the effective DTUs of each of the other performance levels.  The numbers highlighted in green show these results.

Discussion

It is interesting just how closely these results align with the number of DTUs allocated per performance level in Azure.  It is clear that the CPU resources allocated to each performance level in Azure SQL Database very closely reflect the DTU values stated by Microsoft.

By this calculation, my desktop machine offers the equivalent (in terms of CPU resources) of 500 DTUs!  I was a little surprised that even a P2 database has less than half of the CPU resources of my development machine.  Though, as I said, my development machine is still moderately high-spec (and probably way overpowered in CPU terms) and of course, my development machine does not offer any of the other great features in Azure SQL DB!

P2 Query Plans

In some of the test results, P2 appears to only offer around 150 DTUs.  These are the cells highlighted in red above.  This was actually due to Azure SQL DB using a slower query plan when executing these test cases.  For all of the other tests, the sys.dm_db_resource_stats DMV showed 100% CPU utilisation when running the tests.  For the test cases highlighted in red, CPU utilisation was reported at being only around 70%, hence the query took longer to execute.  From the 17500 row test case onwards, the faster query plan was used.  The changeover between the two plans can be seen clearly in the CPU Time results.  Normally, in a given performance level, as the row count is increased there is a significant CPU time increase too.  However, while 15000 rows required 64 seconds to execute, 17500 rows required 66 seconds, i.e. only two seconds more, as a result of the switch to the faster query plan.  These are the two timings highlighted in orange in the table above.

Memory Tests

The maximum possible memory usage under each of the performance levels can be estimated using the method described in my earlier post.  This method was used again to obtain estimates for V12:

Results

These results show some significant changes to the memory allocated to each performance level between v11 and v12.  S1 and S2 are able to access a significantly higher amount of memory in v12.

Interestingly S3 and P1 are allocated the same amount of memory, which mirrors the fact they both have the same DTU rating of 100.  This does mean a significant reduction in the amount of memory available under P1, however this change does make some sense.

Under v11, the memory allocated was solely determined by service tier (i.e. Basic, Standard or Premium).  All databases within a given tier had the same memory allocated, irrespective of the performance level.  Under v12, this has become more fine grained, being based on both service tier and performance level within the tier.  With this in mind, the change to P1 memory allocation can be seen to be more like a correction.  Under v11, P1 was over allocated relative to P2 (or, from the other point of view, P2 was under-allocated compared to P1).  The same applies to S0, S1, S2 and now S3.

Conclusion

Version 12 of Azure SQL Database has brought many significant performance increases over current v11.  The read rate improvements for S1 and S2 are significant.  The read rate improvements for P1 and P2 are dramatic.  The write rate improvements are also significant, especially again for the Premium Tier.  Finally this lifts P1 performance well above that of Web/Business.

The most significant questions raised by these tests are around S3.  S3 write performance is less than S2 – surely a bug/issue.  There also appears to be some volatility around the S3 read rate consistency currently, hopefully this will settle down shortly.

For customers looking to migrate from Web/Business, S3 still looks promising, however the question of the S3 write rate needs answering.  If this is not rectified, then moving from Web/Business to S3 will incur a roughly 50% cut in the write rate available.  If affordable, Premium now offers a level of resources significantly / dramatically above that available in Web/Business.

These tests have shown how the DTUs allocated to each performance level now closely reflect the resources available in each tier.  For the memory and CPU allocation there is close to a direct correlation.

Edit:  2nd Feb 2015:  Additional Test Results

I have run a couple of additional tests (following the same methodology) since the rest of this post was written that have thrown up some interesting results.  I’ll walk through them:

Massive P2 Read Rates

I conducted a further short series of P2 read tests that have shown dramatically different read rates.  I repeated the tests in the other tiers which did not show any significant increase.

1. Repeat of P2 Read Test:  Average Read Rate = 39,200 MB per minute
2. Repeat of P2 Read Test:  Average Read Rate = 39,800 MB per minute
   Clearly a massive increase on the earlier tests.  The read rates were sustained throughout the tests.  Several hours elapsed between the database copies being created and the tests being run (as in the earlier tests).  Buffer pool was empty at the start of the tests.  Perhaps the effect of data in a storage cache?
3. Repeat of P1 Read Test:  Average Read Rate = 12,000 MB per minute
   i.e. matches the earlier tests described in the main body of the post.
4. Repeat of S2 Read Test:  Average Read Rate = 580 MB per minute
   i.e. matches the earlier tests described in the main body of the post.
5. Repeat of S0 Read Test:  Average Read Rate =  292 MB per minute
   i.e. matches the earlier tests described in the main body of the post.

Effect of NOLOCK Hint

Using the NOLOCK hint (where practical) can increase the read rate, significantly so for the premium tier:

1. P2 Read Test:  Average Read Rate = 69,000 MB per minute
2. P2 Read Test:  Average Read Rate = 60,900 MB per minute
   Almost double the P2 read rate result from earlier in the same day.
3. P1 Read Test:  Average Read Rate = 13,800 MB per minute
   i.e. around 2,000 MB per minute above the earlier result.
4. Average Read Rate = 750 MB per minute
   i.e. around a 170 MB per minute increase over the earlier result.
5. Repeat of S0 Read Test:  Average Read Rate =  340 MB per minute
   i.e. around a 50 MB per minute over the earlier result.

Appendix: Additional Read Test Resource Usage Charts

See text above for explanation.

The Basic and S1 charts were very similar to the S0 chart, i.e. close to 100% read utilisation throughout (sorry, just too many charts to produce to create and include them all!).

Appendix: Additional Write Rate Consistency Charts

See text above for explanation.

Since Microsoft released the Azure SQL Database v12 Preview in mid-December, some users have suggested there was, around the same time, a noticeable drop in the performance of the legacy Web/Business editions of Azure SQL Database.

In this post I re-run some of my earlier tests to see if that really is true.  I was planning to revisit Business Edition in any case, to get some better and more up-to-date metrics for comparison to V12 when that heads into General Availability.

July 2014 Performance

The Sequential Selects read tests performed in July 2014 estimated that Web/Business Editions read from physical disk on average at 550 MB per minute (peak 650 MB per minute).  See those earlier tests here for details.

Several write tests were also performed in July 2014.  All were reasonably consistent and estimated the maximum write rate at around 90 MB per minute.  See for example the Bulk Load tests here.

Quick Test

Before setting up any elaborate tests, I ran a quick initial read test – basically a sequential read of the whole of a 37 GB database.  This took 2 hours 50 minutes, equating to a read rate of around 225 MB per minute.  Clearly, much less than the July 2014 results and a sufficiently big enough difference to warrant further investigation.

Re-running the Sequential Select Read Tests:  Read Rate into a VM

The first full tests to run were a repeat of the July 2014 Sequential Select Tests.  This should allow a direct comparison between performance in July 2014 and January 2015.

The Sequential Select test involves reading an entire database to get a simple estimate of maximum read rates. The data is read table-by-table, and each table is read sequentially (i.e. roughly speaking SELECT * with no ORDER BY).

The test involves using a newly restored database. The database is 36 GB in size.  The test lasts 60 minutes.

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.

The data is read into a Windows Azure Worker Role (i.e. a VM in the same Azure data centre) which measures the data read rate and captures various other metrics.  The test uses a single reader (i.e. a single thread) which is typically sufficient to hit the read rate limit (more details on this below).

Results

The chart shows three different runs of the same Sequential Select test.  The read rate is quite volatile, flipping between around 200 MB per minute and around 500 MB per minute.  The first test ran almost entirely at 500 MB per minute, the second test mostly at 200 MB per minute, the third test flipped between the two rates.

Comparing with the July 2014 test results:

The read rate was much more stable in the July tests at around 550 MB to 600 MB per minute, though not completely stable – the third Business Edition test shows a drop to 200 MB per minute, though it is brief.

This suggests Business Edition read performance has become more volatile since July 2014.

Alternative Read Test:  Maximum Read Rate Test inside SQL Server

The older test, described above, was based on reading from the test database into a Azure Worker Role – i.e. a VM outside SQL Server.

The more recent tests I have conducted against the newer service tiers in v11 and preview v12 (see previous blog posts in December 2014) involved measuring the read rate using a simple aggregate query (i.e. select count(*) from … where … ).  This performs table scans across an entire database but the data does not leave SQL Server (only the final count(*) value is returned by the query).  Compared to the previous test, this test has the advantage that it does not involve any significant network activity between SQL Server and a VM outside of SQL Server which may have constrained the read rate in the earlier test.  This should therefore allow a better estimate of the maximum read rate from disk, free of any network constraints.  For more details of this test, see the “Test 1 – Sequential Table Scans by a SQL Query from Physical Disk into Memory” heading in the previous post here.

Results

Above: The data was read from the database table-by-table.  Each table was 1 GB in size.  The time taken to read each GB was measured.  The chart above shows the read-rate for each GB of data read during the test.  E.g. we can see the 24th GB in test 2 was read at a rate of around 275 MB per minute.  The test was repeated three times.

The flipping between two different read rates is even more clearly shown in this test.  The maximum read rate here is actually 800 MB per minute, higher than the 550 MB per minute seen in the earlier tests.  As suggested above, this higher rate is very likely explained by a network I/O restriction somewhere between SQL Server and the Azure Worker Role VM used in the earlier test.

However, more interesting in these results, is the fact that the flipping between the two different read rates is even clearer here.  This suggests internal logic in Business Edition that, once a given SQL Server is overloaded (i.e. when all of the different databases from all of the different users on that server attempt to read too much data too quickly), then the read rate for some/all databases on that SQL Server is limited to 200 MB per minute until the overload passes.  This is probably a simpler mechanism that provides a more reliable database service than failing databases over to other SQL Servers to attempt to balance workload (causing outages in the process).

Re-running the Scale Tests

The Scale Tests from July 2014 involved executing a mixed workload of inserts, (single and multi-row) selects, updates and deletes against copies of the same 36 GB test database.  This test was re-run as it provides a more complex workload than a simple sequential read – which is clearly not a typical workload.

Again, the test starts with a newly-restored database.  The requests are not uniformly distributed across the database (mirroring most typical OLTP workloads) so that the SQL Server Buffer Pool should have an increasing effect as the test runs and more data is cached in memory.  See the earlier post here for more details on this test.

In July 2014 tests were performed against Business Edition with different numbers of worker threads (i.e. number of concurrent requests).  The test re-run here is the heaviest of those tests – the 8T test from July 2014 – i.e. 8 worker threads running in parallel in the VM, generating requests against the test database.

Results

The lines on the chart show the number of operations (i.e. SQL statements) executed per second.  The blue lines are the July 2014 results.  The test was run three times in July 2014.  The green lines are the January 2015 results – again the test was run three times.

Clearly, performance is comparable between July 2014 and January 2015.  The performance is quite volatile in all six tests (as is to be expected in Business Edition where all users share/compete for server resources).

Averaging the three results from each set of tests:

The latter stages of the test perform better in the more recent tests.  There is no difference in the types of request generated as the test progresses.  The only thing that changes as the test progresses is that the SQL Server Buffer Pool will have more data cached (i.e. the cache starts empty and fills as requests are processed).  Therefore, this difference between July 2014 and January 2015 could be explained by an increase in the size of the SQL Server Buffer Pool per database since July 2014 allowing more data to be cached – or it could simply be a random result given the inherent volatility in Business Edition.

Write Rates

The write test conducted involved using a SQL query to write 100 MB chunks into a database, up to 10 GB in total, and measuring the write time for each chunk.  The query was executed directly on the server – i.e. no external VM and no VM to SQL Server network hops were involved.  Again, the test was executed three times.

Results

Above: The data was written to the database in 100 MB chunks, up to a total of 10 GB.  The time taken to write each 100 MB chunk was measured.  The chart above shows the write-rate for each chunk written during the test.  The test was repeated three times.

Clearly, the write rates are quite volatile (just as they were in July 2014).  Smoothing (via a rolling average):

The average write rate in the first test was around 87 MB per minute.  In the second and third tests the average rate was almost exactly 100 MB per minute on average.  Entirely consistent with the July 2014 results.

Conclusion

The Business Edition of Azure SQL Database has always featured / suffered from variable performance.  These tests appear to show the performance is more volatile in January 2015 compared to July 2014.  However there is no clear evidence for performance having been “turned down”, e.g. no lower maximum performance limit has been imposed.

The ratio of SQL servers to user databases in Azure SQL Database has likely always varied as new user databases and new batches of servers are deployed at different times.  It is probable that last July there was more spare capacity and a so a lower database density (i.e. number of databases per server) across the SQL servers in the Azure data centre.  Now, it is possible that the database density has risen and as a result performance for individual databases is being trimmed back more often to balance workloads (such as the flipping between higher and lower read rates seen in these tests).

Conspiracy theorists may suggest Microsoft have increased database density to reduce performance.  Personally I think that is unlikely.  Business Edition has always been volatile and some users have always had to endure periods of more volatile / lower performance.

Indeed, the entirety of this post shows why Business Edition is difficult to work with and will inevitably lead to difficulties for/between Microsoft and users.  Performance varies and drifts over time and there are no performance benchmarks to plan against, test against, measure against, etc.

The long term solution to this variable performance is to move into one of the newer service tiers where the performance boundaries between different databases are much more rigorously enforced.  Previously this would have incurred a much greater cost compared to the Business Edition cost.  However, the new S3 service tier in the upcoming Azure SQL Database v12 (currently in preview) should help here, providing a closer price/performance fit to Business Edition than has been available to date.  See my earlier post here for more details.

And Finally…

These tests were carried out in the Western Europe Data Centre.  It is possible performance may be different in other data centres (e.g. where the ratio of SQL Servers to user databases may be different).

Update 31st Jan 2015:  For updated performance information describing v12 at General Availability – please see the newer post here.

Last week Microsoft announced the new V12 Preview of Azure SQL Database.  This introduces a new S3 performance level and brings to Azure a considerable number of on-premise features that have been missing to date.  These reasons alone make it a very significant update.

I have now performed some quick performance tests on V12 which have demonstrated some very substantial performance improvements.  I have also repeated some of my earlier tests against the current generally available V11 version of SQL Database to provide a direct V11 to V12 comparison.  I will summarise the tests and results below.

Important:  These results are very likely only valid today, for the particular test cases described here.  Given V12 is in preview Microsoft will almost certainly be adjusting performance levels until General Availability.  Please bear this in mind and remember any numbers here describe preview performance at the time of writing only.

Microsoft publish very few absolute performance metrics regarding Azure SQL Database.  The tests described here involve indirectly calculating absolute metrics.  Methodology is described below.

For reference, these tests were conducted against the following Azure SQL Database version (select @@version):

• Microsoft SQL Azure (RTM) – 12.0.2000.8   Dec  8 2014 05:06:51

Read Rate Test

In my earlier posts I have described on several occasions how the read rate limits inherent in the newer service tiers (as opposed to Web/Business) will, for many databases, be a significant limiter on overall performance.  With that in mind, I am pleased that V12 appears to show very significant increases in read performance.

Test 1 – Sequential Table Scans by a SQL Query from Physical Disk into Memory

This test aimed to measure the maximum rate that data can be read from physical disk into the Buffer Pool.

The test involved using a set of test data that is likely much larger than the SQL Buffer Pool sizes:

• Basic Edition: 2 GB database
• Standard Edition:  20 GB database
• Premium Edition:  30 GB database

A new database was created in each service level and populated to the sizes given above.  The database was then copied (using the Database Copy facility).  My understanding is that this performs a physical copy of the database files (determined from some other tests I have undertaken previously) and results in a new database which has an empty buffer pool i.e. no data cached in memory.  The test then involves sequentially reading through the database reading data from disk allowing the maximum physical disk read rate to be measured.

Data was divided into 1 GB tables and each table was read sequentially.

The SQL statements used to read through the database were similar to the following:

select count(*) from Table0
where [Bit1] is not null and
[TinyInt1] between 10 and 240 and
[Int3] between 100000 and 2000000000 and
[Money2] between 0 and 10000000

No indexes (other than a clustered index on the RowId primary key) were present on the tables.  A count(*) was used to ensure performance is not held back either by complex processing or by waiting for slow network I/O returning results to the client.

Results

The blue bars show performance values obtained in my earlier tests in July during the preview of the newer service tiers (Basic, Standard and Premium).  The orange bars show performance in the current generally available version (i.e. V11) of SQL Database.  V12 preview performance is shown in the green bars.  Premium P1 and P2 were also tested:

Now you can see why the Premium results needed to be split into a separate chart.

These results show a few interesting things. Firstly, the very substantial read performance increase in Premium edition.  Prem P2 was reading at 14 GB per minute in this test compared to around 800 MB per minute in the July tests.  My first impression on seeing this result was that perhaps data was somehow in the buffer pool prior to the test, thus making the result appear far too high.  However, I retrieved the query statistics from the query plan cache which reported that 30 GB of physical reads had occurred during that test, so the result appears valid.

It is also interesting to note that V11 performance has increased between the time of my July tests (when Basic, Standard and Premium were in Preview) and today.  This is partly not surprising since Microsoft state that performance will be adjusted during previews. I was however partly surprised since the changes between July and December in V11 are significant (i.e. well beyond minor adjustments).  And these changes occurred without any public announcement regarding a change in DTUs in each performance level – i.e. the stated DTUs in each performance level at the time of my July test are the same as the DTUs today (with the exception of a minor adjustment in Standard Tier to accommodate the introduction of S0).

Overall the V12 preview performance represents a big improvement over V11 and a massive improvement in the case of Prem P2.  Remember however that Microsoft may adjust the performance level between now and GA.

Side Note:  Revisiting July Tests

I reviewed my earlier tests to check if any mistake had been made in the earlier calculations though all seems correct.  For example, during my July P1 read test, the sys.resource_stats DMV was reporting 100% disk read utilisation at around 400 MB per minute.  This firmly suggests Microsoft have made some adjustments in V11 at some point between July and today (probably at GA in September), without any announced DTU change.

Test 2 – Sequential Read Rate Into an Azure Worker Role

The tests described above are a little different to the earlier tests I conducted in July.  My earlier tests measured the read rate into an Azure Worker Role i.e. into a separate machine as opposed to just reading into SQL Server Memory (as described above).

So I also repeated the earlier Sequential Select tests for comparison purposes.  These tests also demonstrated a very significant increase in performance, e.g. a P2 database could be read at 2.2 GB per minute using 8 reading threads in an A4 sized Worker Role, and a P1 database could be read at 1.5 GB per minute using 4 reading threads in an A3 sized Worker Role.

I don’t believe these tests were able to push the maximum read rate of the V12 preview.  During these tests the sys.dm_db_resource_stats was not reporting anything close to 100% in the avg_data_io_percent column (unlike in the July tests, where close to 100% was reported throughout).  This may be due to hitting a network I/O limit between the SQL Server and the Worker Role in the Azure data centre or possibly a network I/O restriction on the SQL Server.  (CPU in the worker role was not an issue – this was monitored during the tests).

Nonetheless, these tests also demonstrate a very significant performance improvement.

Read-rate metrics were captured minute-by-minute through these tests and displayed the same level of consistency as seen in the earlier tests – i.e. that the newer service tiers are much more consistent than the legacy Web/Business editions.

Comparison To Web/Business Edition

For quick comparison purposes, I have included one of the Web/Business Edition test results from my earlier tests in the charts above in this post.  It should be made clear that the Web/Business rate is for a single thread/connection – i.e. a single thread/connection could read, on average, at 550 MB per minute from Web/Business Edition in my earlier tests.  Web/Business performance was typically quite volatile throughout the earlier tests (for example see the chart here), but this was the average value reading from a Web/Business edition database into an Azure Worker role.

On the other hand, the rates for the newer Service Tiers are total limits per database.

This means when opening multiple connections to Web/Business it is sometimes possible to obtain rates much higher than 550 MB / minute.  Therefore a direct comparison is difficult, and hence this note, which hopefully makes understanding the comparison a little easier.

Reviewing the V12 Preview results I believe Microsoft are now moving towards pitching S2 and S3 as viable replacements for some of the smaller workloads in Web/Business.

I have not performed any new tests on Web/Business at this time (not enough time in the day!).

Write Rate Test

Test 1 – Large Inserts by a SQL Query from Memory

This test aimed to cache a relatively small amount of data in the buffer pool (100 MB), then repeatedly write this until a specified total data volume had been written.  Each 100 MB chunk was written using a single Insert statement.  The total data volume was specified to aim for a test very roughly around 1 hour in length  The time taken would then allow an estimate of the maximum write rate.  Total data volumes used were:

• Basic and S0 – 1 GB.
• S1, S2 and S3 – 2 GB.
• P1 – 5 GB.
• P2 – 10 GB.

Results

These tests also demonstrated significant write rate improvements compared to V11.

It is interesting to see that the standard tier performance levels appear to have slightly odd write rates in the V12 preview.  S2 actually slightly outperformed S3.  S0 and S1 were broadly similar.  I re-ran these tests twice and obtained similar results.  This is surely evidence that performance levels are still being adjusted by Microsoft and are likely to change.

For the write rates, the July 2014 results are close to the V11 December 2014 results.

Test 2 –  Bulk Load from an Azure Worker Role

Again, a second set of tests was conducted to mirror the July Bulk Load tests.  This involved using the SQL Server Bulk Load interface (via the C# SqlBulkCopy class) to write into SQL Server using multiple threads from an Azure Worker Role.

These results were comparable to those shown above.  The sys.dm_db_resource_stats showed that these tests were able to push V12 to 100% of the log rate limit.  Since even the highest I/O rate was only 250 MB per minute, there was no external limiting factor such as network bandwidth constraining the write rate from the Worker Role, so allowing it to roughly match Test 1 above.

Write-rate metrics were also captured minute-by-minute through these tests and again displayed the same level of consistency as seen in the July tests – i.e. that the newer service tiers are much more consistent than the legacy Web/Business editions.

Memory Limits

The maximum memory usage under each of the performance levels can be estimated using the method described in my earlier post.  This method was used again to try and obtain estimates for V12.

The sys.dm_db_resource_stats DMV was not reporting memory usage information for the S3, P1 and P2 performance levels.  Thus the maximum memory limit could only be estimated for the Basic, S0, S1 and S2 performance levels:

• Basic – 256 MB = no change from current V11 value
• Std S0 – 512 MB = no change from current V11 value
• Std S1 – 1024 MB = compared to 512 MB currently in V11
• Std S2 – 2560 MB = compared to 512 MB currently in V11

Clearly, these are steps in the right direction and may make S1 and in particular S2 perform much better for some workloads than has previously been the case under V11.

Conclusion

This post has been a very quick run through some performance tests against the new V12 Preview.  Pleasingly, I/O performance, especially read-performance, seems significantly better in V12.

It will be interesting to see where the S3 I/O performance level settles at GA, since S3 may well be the cost-effective option for many existing Web/Business users for whom Premium P1 is simply too expensive (to the point of otherwise potentially leaving Azure altogether for these workloads).  That Microsoft have already said S3 will run at 100 DTUs is a good sign that it will land in this area.

The Premium Service Tier has, in the past, been viewed as very expensive for the level of performance that it delivers.  If the performance at V12 GA lands as it is currently running in Preview, then Microsoft will have made a clear step forward in addressing that criticism.

This is a very brief post since I am currently busy with other things and haven’t had time to test the Azure SQL Database V12 Preview yet.  In short, V12 brings a whole heap of missing “on-prem” edition features to Azure and introduces a new performance level, S3.  All great things.  I am however a little bit confused about the story of the S3 performance level.  I’ve posted a comment on Scott Gu’s blog post:

I am happy to read about the improvements to Azure SQL DB. Excellent news that the on-prem/IAAS vs. PAAS feature gap is closing in a big way. I do have a couple of questions though:
We now seem to have both S3 and P1 at 100 DTUs, but with very different prices, which seems odd. Even though P1 offers numerous other/improved features over Standard Tier, the price difference seems very large given the “DTU” equality.
Further, it is stated that P2 and P3 are getting I/O improvements, but there is no mention of changes to the DTUs to these tiers. If actual performance (i.e. IOPs) is changing, then presumably DTUs should be increased (unless the definition of a DTU itself is changing). I am a bit confused. The story here seems incomplete. Can you help clarify things?

It almost seems as though there might (pure speculation) be some other as-yet unannounced changes to the other existing Standard/Premium service tier performance levels coming to straighten this out (perhaps at the V12 GA?).  Maybe, then again maybe not – the whole DTU story has always been a little bit confusing to me!

Hopefully I’ll get some time to take V12 for a test drive over the next couple of weeks.

The new service tiers in Azure SQL Database provide different levels of performance, by offering more of the following at each higher service level:

• Processing (CPU) Time
• Read Rate (from physical disk)
• Write Rate (to the database log file).
• Memory (space to cache and process data)

One of the frustrating aspects of the new service tiers on a technical level is that Microsoft are not providing any particularly meaningful performance units to measure the new tiers by.  Everywhere you look you find relative units, be it DTUs or be it percentages that measure resource utilisation in each tier or compare between tiers (e.g. in the sys.resource_stats / sys.dm_db_resource_stats DMVs in the master / your database respectively).

My earlier performance tests provided some information about the maximum read and write rates supported by each tier.

I’ve now performed some further tests that provide some information about the memory limits in each tier / performance level.  Results are given first.  The methodology is at the end of this post.

Test Results – Azure SQL DB Memory Allocation

The tests suggest each database is allocated memory as follows:

• Web / Business Edition – at least 5 GB (likely variable).
• Basic Tier – 250 MB
• Standard Tier S0 – 512 MB
• Standard Tier S1 – 512 MB
• Standard Tier S2 – 512 MB
• Premium Tier P1 – 9 GB
• Premium Tier P2 – 9 GB

Premium P3 was not tested.  It is not possible to obtain exact figures for Web/Business edition since the DMVs don’t contain memory information in that edition.  However, repeating the same tests has proven that it is possible in Web/Business edition to cache at least 5 GB in memory. More on this below.

This test also allowed some write-rate metrics to be captured.  These are more or less identical to those captured during the earlier tests, i.e. nothing has changed here.

Thoughts

Standard to Premium Comparison

The most striking point is the relatively big jump between the Standard Tier and Premium Tier.  Comparing S2 to P1:

• Price per Month:  75 USD vs. 465 USD = a big 6 times increase
• Read Rate (MB/min):  140 vs. 400 = a 3 times increase, i.e. smaller than 6
• Write Rate (MB/min):  38 vs. 48 = only a slight increase
• Memory:  512 MB vs. 9 GB = a massive 18 times increase.

Clearly, much of that 6 times price increase is going towards getting a relatively speaking much bigger slice of the memory pie (plus the other additional features in Premium around HA, etc).

Standard to Web/Business Comparison

Running the same tests on Web/Business edition showed this edition was able to cache at least 5 GB in memory.  I.e. at least ten times that of Standard Edition.  Upon reaching this point, I stopped trying to cache any additional data.  Given the competitive manner in which different databases compete for resources in this edition, it is possible further tests could disrupt other workloads running on that server.  However, even when Web/Business edition is performing badly, it still probably has more than 512 MB available per database.

Like the earlier I/O tests, these tests further underline that Standard Edition, despite the name, is not a like-for-like replacement for Web/Business Edition for larger databases and heavier workloads.  The story is the same – Standard Tier does guarantee resources, but the maximum available is much less than that available in Web/Business Edition.

Standard Tier

It is interesting to note that standard tier has the same amount of memory allocated across all three performance levels.

Though, again, I would be interested to see just how many real world databases are able to perform well in Standard Tier when they are actively used databases and anything more than a few GB.

Microsoft’s DTU Benchmark

The above reinforces my thoughts about Microsoft’s own DTU Benchmark from my earlier posts.  I include it again here:

Microsoft’s decision to test with relatively small databases will no doubt have boosted the DTU benchmark rate given the much lower maximum memory allocated to Standard Edition.

Basic Tier

In my Sequential Select tests back in July, the Basic tier was able to cache my entire 2 GB test database.  Clearly, during the preview, the memory limits (in the Basic tier at least) were either not in place or were much higher.

Test Methodology

Indirect Testing

The most important point to note here is that there are currently no means to directly measure the amount of memory allocated to a database in Azure SQL DB – i.e. no DMV, performance counter or similar.  Instead, an indirect test can be used to infer the amount of memory.  Using this method we cannot say the result is 100% accurate, however I believe these results will likely be very close.  In addition, the resulting numbers look very plausible (which proves nothing of course, other than the fact they don’t look wildly, ridiculously wrong!).

The test approach used was as follows (repeated for each service tier performance level):

1. Create a database locally containing just one table.
2. Populate that table with some data (200 MB for the Basic tier, 250 MB for the Standard tier and 1 GB for the Premium tier).
3. Export the database to a BACPAC.
4. Import the BACPAC into Azure SQL DB to a new database in the correct service tier (e.g. if running an Standard S2 test, then into a new S2 database).
5. Rebuild the table to remove the fragmentation created by the BACPAC import process (this step is probably not strictly necessary for the test).
6. Wait a short time until the database is restorable (newly created databases aren’t restorable immediately) – i.e. until the Restore button in the management portable is enabled.
7. Drop the database.
8. Restore the database.  This clears out any junk in memory and means the database essentially starts with an empty buffer pool, no cached query plans or other memory usage, etc).  This can be verified by checking the sys.dm_db_resource_stats DMV which will show close to zero memory usage.
9. Run a simple query that performs a full table scan.  This should load the entire table into the buffer pool.  This will take some time to run, since the data is being from disk into memory.  Because Azure SQL DB relatively heavily restricts I/O, this will likely be of the order of a minute or so.
10. Immediately after the query finishes, check the sys.dm_db_resource_stats DMV to see the % amount of memory used
11. Find the size of the table (this query is also given below).
12. Assuming all of the table data is now in the buffer pool, the total memory allocated to the database can be estimated using:
    Total Memory = 100 / (% memory used) * (size of table).
13. Check that all of the table data is still in the buffer pool by re-running the query from step 9.  If the data is still cached, the query should now return in just a second or two.  In all of my tests, the second, third, etc runs of this query took one to two seconds (compared to between 40 seconds and 3 minutes for the first runs of the query, depending on the service tier).

Queries

The query I used in step 9 was:

select count(*) from Table1
where [Bit1] is not null and
[TinyInt1] between 10 and 240 and
[Int3] between 100000 and 2000000000 and
[Money2] between 0 and 10000000

The count(*) is very simple for SQL Server to calculate.  It requires very little memory to process (which is good, because if it required lots of memory, that would confuse the memory figures in the DMV used for our calculation).

The where clause is just some random criteria to force a table scan, i.e. all of the data to be read into memory.

The query I used in step 11 was:

select 
 schema_name(sys.objects.schema_id) +'.' + sys.objects.name as ObjectName, 
 sum(reserved_page_count) * 8.0 / 1024 as ObjectSizeMB
from sys.dm_db_partition_stats, sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id and 
schema_name(sys.objects.schema_id) <> 'sys'
group by schema_name(sys.objects.schema_id) +'.' + sys.objects.name
order by 2 desc

Result Data

The following lists the table size and corresponding percentage of memory used (from the sys.dm_db_resource_stats DMV):

• Basic Tier:  198.1 MB, 79.4%
• Standard S0:  253.6 MB, 49.5%
• Standard S1: 253.6 MB, 49.6%
• Standard S2: 253.6 MB, 49.6%
• Premium P1: 1049 MB, 11.47%  (this DB was slightly larger than the P2 database as the REBUILD failed, I did not have time to re-run the REBUILD so skipped that step in the test method – this result nonetheless is consistent with the P2 result).
• Premium P2: 1010 MB, 11.03%

Web/Business Edition Tests

There is no DMV available in Web/Business edition to calculate the maximum amount of memory available to a given database.  Nonetheless, the exact same test process∗ was followed (with 1 GB, 2 GB and 5 GB data sizes).  The dramatic time difference between first and subsequent executions of the test query (as described in step 13 of the test method above) shows that the data was still cached.  Even the test query against the 5 GB test data set returned in around four seconds (compared to four minutes on first run).  A dramatically more impressive performance than Standard Tier, though as ever performance will probably be variable in Web/Business Edition.

∗ with the exception that instead of DROP/RESTORE (which is not possible in Web/Business) a database copy was created.  This has the same effect as the DROP/RESTORE – i.e. it results in a new database with close to zero memory usage.

Today I have made the tool available via this blog.  It is called SQL Workload Profiler:

The tool aims to to provide performance statistics for the queries running in your SQL Server minute-by-minute (or less), together with a quick and simple analysis interface to allow that data to be explored.  The captured granular statistics can also be written to a SQL Table during data collection for saving, more detailed analysis, etc.

Many more details can be found over on the SQL Workload Profiler page.

Performance Metrics

Update (31st January 2015):  For current Performance Metrics, please the current performance test results here (for both v11 and v12).

Changes at General Availability

I will also call here that the pricing for Standard Edition at GA has been reduced compared to the prices stated during the preview.  This is a good step and actually makes the move into the new Service Tiers attractive for lightly loaded databases for cost reasons alone (even ignoring all the other great new features in the new Service Tiers) – more thoughts on this below.

Changing Your Thinking When Approaching the New Service Tiers

The new SQL Database service tiers require that we, as customers, adopt a completely different mind-set in how we approach SQL Database. The higher performance that we want, the more we have to pay. And that performance scale, at the bottom end of the new service tiers, starts very small compared to Web/Business. Standard Tier, even S2, offers on average, significantly less resources than Web/Business.

At the top end of this scale, the resources available reach significantly higher than Web/Business – P2 offering on average more resources than Web/Business.  P1 is a mixed comparison  – sometimes roughly equal to Web/Business, sometimes less than Web/Business, depending on the specific performance metric and how exact you want to be.  P3 (not tested in my performance tests) presumably provides resources way above Web/Business.

But, having said the above, remember this: Your database may perform well on Standard Tier, it may not.  It all depends on how much performance you need.

To be more specific, it depends on how many resources you need to operate your workload.  Higher service tiers offer more IO, CPU, and memory.  I talk “performance” in this post to be more conversational, in the same way people talk about say a “high performance car” as opposed to a “well resourced car”!  But really, performance is “resources divided by workload”, so, for a given workload, higher performance needs more resources.  In Azure SQL Database terms, this means small workloads can obtain good performance on the lower tiers.  Larger workloads need the higher service tiers.  The rest of this post will hopefully help make this picture a little clearer, talking a bit about performance, resources (i.e. service tiers) vs. workloads and how Web/Business compares to the new service tiers.

Consistency of Performance

In all of the talk about the great new features in areas such as geo-replication, self service restore, etc it is easy to lose sight of probably the biggest improvement in SQL Database in the new Service Tiers – consistent performance. This was/is the biggest driver for the creation of the new Service Tiers to replace Web/Business.

The both long-term and short-term random performance dips that SQL Database has struggled with due to noisy database neighbours on the same server should be a thing of the past. The tests I conducted showed that the new tiers do offer more consistent levels of performance over the course of a short test – much less volatile than Web/Business.  It’s “just” that now, to access those higher levels of performance means paying more cash.

As an example that really makes this clear, the following two charts show the Bulk Load Performance for Web/Business vs. S2 to P2.

The “xT” figures refer to number of threads (concurrent database connections) running in each test, e.g. “Business 4T” means a test running with four threads against a database in Business Edition of SQL Database  Each test was run three times (UT1, UT2 and UT3).

The chart shows Web/Business performance is very volatile – on average around 80 to 100 MB/min.  At the extreme, at minute 23, the “Business 4T – UT3” test (grey line) is writing at 140 MB/min, at minute 28 this same test ground to a complete halt at zero MB/min.

The new service tiers by comparison are generally speaking much more consistent.

The “xT” figures refer to number of threads (concurrent database connections) running in each test, e.g. “Prem P2 4T” means a test running with four threads against a database in the Premium P2 service tier.  Each test was run three times (UT1, UT2 and UT3).

Web/Business was writing quite wildly at a rough average of 80 to 100 MB/min.  S2 by comparison is writing consistently at 40 MB/min, P1 at 50 MB/min and P2 at 90 MB/min.  So the average rate of Web/Business is higher than S2 and P1 (for this test) but the rate was much more volatile.  For more details see the Bulk Load Test post.

Which Service Tier is Right for My Database?

My view, very roughly speaking, is that for a databases in the tens of GB (i.e. 10, 20 GB and above), if you were using the full performance available in Web/Business, to reach on average the same performance level as Web/Business will require P1 or P2. Smaller databases may be able to reach and even exceed Web/Business on Standard Tier in some cases.

As ever, it will depend on your specific workload.  Microsoft have released a new DMV in Azure to allow you to see how often a database is hitting the resource limits of the tier that it is hosted in.  See the following blog post for more information:

http://azure.microsoft.com/blog/2014/09/11/azure-sql-database-introduces-new-near-real-time-performance-metrics/

Also, right now, today, you can take a look at the resource utilization figure in your Azure Dashboard.  This is available for Web/Business databases too.  Note, I believe this is only visible in the Preview Dashboard, not the full azure portal.  (To reach the new portal, when logged into the full azure portal, select “Switch To New Portal” from the drop down menu available when clicking on your login email address at the top right of the screen).

This figure is provided as a percentage of the S2 tier capabilities.  E.g. 16% = resource usage at 16% of the maximum resources available if the database was hosted in the S2 Service Tier.  These values can be above 100% (a big improvement over the values in the preview that were limited to a maximum of 100).  This is especially useful, e.g. 200% means resource usage at twice the maximum resources available in S2, so start considering a P1!

Only a single figure is provided in the dashboard.  Look in the sys.resource_stats DMV in the master database in your existing Web/Business SQL Server for more details.  The avg_cpu_percent, avg_physical_data_read_percent, avg_log_write_percent, etc columns here provide more granular usage data.

In advance of you performing analysis, speaking very roughly, the disk IO throttling (and especially the read throttling) on the new service tiers will often be the most significant impact on performance. For databases with only a small “hotspot”, SQL Server caching will likely help you avoid the worst of the read throttling and performance should still be good, even on Standard Tier, e.g. your database is 200GB, but only 500MB of that data is being read (with the rest purely historical and never accessed).

However, I would be very cautious when considering running a database over 10GB, 20GB, etc. in Standard Tier. If your database users step outside of the “hotspot” referred to above, then performance may dramatically drop (e.g. if queries perform scans of large tables). Also, database maintenance operations (e.g. rebuilding indexes, importing/exporting a database) can be very painful on the Standard Tier since they too are subject to the same throttling limits. Of course, you do have the option of switching up to the Premium Tier for these operations.

Equally, if your database workload is very write-heavy, CPU heavy, or memory-intensive then you probably will hit these limits before the read-throttling has any effect.  Profiling your workload and making use of the DMVs described above is the way to find out.

The following example illustrates how bad performance can be on the wrong tier, and how observed performance doesn’t really scale with DTUs. The chart shows how the results of running a mixed workload at scale for two hours. This workload consisted of a mixture of single row inserts, selects and deletes, plus some relatively small multi-row selects – against a 36GB database. The “where” clause in all queries was against the clustered index primary key.

The “xT” figures refer to number of threads (concurrent database connections) running in each test, e.g. “Prem P1 4T” means a test running with four threads against a database in the Premium P1 service tier.  Each test was run three times.

Note these results are plotted on a logarithmic scale. Standard Tier performed very badly for this workload, with less than 1% of the throughput of Web/Business and P1. Clearly P1 is the minimum tier for this workload. Note that the Basic database in this test was 2GB (the maximum size limit of that tier). This explains the much better results for Basic compared to Standard. These tests can be read about in more detail on the Scale Tests post.

So, in summary, databases that were running near the performance limits of Web/Business will need Premium P1 or above. And that does equate to a significant price increase.

Microsoft have stressed to me that, from the usage data they have, they believe very few customers will be impacted negatively by the new Service Tiers. I do however struggle to reconcile that view with my test results but, if many customers were not using the full IO available in Web/Business, then that may well be true.

Microsoft’s DTU Benchmark for the New Service Tiers

I found it interesting that Microsoft ran their DTU benchmarking for the new tiers against what are relatively speaking very small databases. E.g. for S2, their benchmark database was only 7GB – very much less than the total 250GB capacity of S2. I interpret this as another reason to be very cautious about running larger databases in Standard Tier.

Roughly speaking, running a much larger database (say 35 GB = 5 times bigger than the benchmark size), where the same percentage of the database is accessed as in the benchmark, likely means a very much lower overall transaction rate than in the benchmark.

Performance Tuning Is Now More Important

Charging by performance is all arguably the way that Azure SQL Database should have been designed all along. Behind the scenes it is logical to assume the overall higher prices mean the SQL Servers in Azure are not so overcommitted under the new Service Tiers. Bluntly, paying more for Premium means less noisy neighbours and more guaranteed server resources allocated to your database.

Unfortunately, this change of approach (from Web/Business to the new service tiers) by Microsoft means a (possibly painful) adjustment for customers. We may be able to mitigate some of that by performance tuning our databases before the move to the new Service Tiers. I suspect Web/Business has been covering up performance issues in some (many?) databases. By the platform throwing more IO at these databases they have been able to run quite happily. When Web/Business is finally withdrawn, if these issues are still present, we will now need to pay more to cover them. Investing time/money in performance tuning suddenly becomes more cost-effective.

With that in mind, it good news that Microsoft have extended Web/Business by another six months from the originally announced end date of April next year. It gives everyone more breathing room for this adjustment.

Read and Write Rates

The table below summaries the maximum read and write rates from my performance tests.

Note that these read and write rates are total database throughput.  They are not per connection rates.

Cost – Performance Comparison

The table below is an updated version of the table that appeared in my earlier post.

For customers with large databases who are not using the full performance capabilities of Web/Business, the Standard Edition pricing is now quite attractive.  E.g. for a customer with a 100 GB database that never uses more than (approx) one third of the performance capabilities of Web/Business, price per month has reduced from $176 under Web/Business to $75 per month under S2.

The new service tiers still represent a significant price increase however for customers who do make heavier use of Web/Business.  E.g. even for a 150 GB database, the price has doubled from $226 (Web/Business) to $465 per month (P1).  For smaller (but still heavily loaded) databases, the price comparison is even worse.  For these customers, the new features in Azure SQL Database (such as geo-replication, self service restore, etc) will have to provide the justification for the move, since cost alone will clearly not.  I rather suspect however, that many of these customers may be forced into looking into alternative options (nb: if you are in this group, please feedback to Microsoft!).

It is interesting to note the very large relative price jump from S2 to P1.  Microsoft have hinted that they were looking at this, considering if customer requirements warranted anything further in this area.

Customer Reactions

I do know that some customers are very upset about the price increases they are facing.  It is human nature to some extent: customers have a view that I pay X to get ABC now, and on the new tiers I now need to pay Y to get ABC.  Where X is much less than Y, that has a high risk of causing a negative reaction, particularly so where that difference is significant in terms of overall spend – which I suspect is more likely to be the case for smaller customers.  For larger customers it may not be significant in terms of total spend or the price increase is still acceptable when considered against the costs associated with on-premise solutions.

To reiterate, my view is that this is the way the service should have been designed all along and paying by database size was less than ideal.  I don’t like the price increases (I am affected too) and believe the S2 to P1 price-performance options still need adjustment.  But, the move to performance based pricing was probably inevitable.

In hindsight, Microsoft would probably admit the Web/Business pricing was a decision made by a rush to get something into the marketplace – measuring and controlling database size is something SQL Server has done for years, it’s quick and easy.  Controlling and segregating performance is more complex and the controls available in SQL Server have only partially existed until now.

Of course, that is little consolation to those customers now facing painful decisions as they make the adjustment to the new reality.

Final Comment

There is a lot to like about the new service tiers in Azure SQL Database.  The greatly improved feature set and more robust performance.  Cost will be an issue for some customers but Microsoft have stressed (for example in the comments on my previous post) that they are listening and have no intention to strand customers.  In my conversations I have found them to be eager to listen and get feedback.

With all of the above in mind I would encourage you to test the new tiers (ideally with a test database or non-critical application in the first instance) as soon as possible and work from there.

This is the final post from 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.

Thanks for reading.  I hope the information shared here will be useful in some way.

Quick Navigation:      << Previous post in the series <<

Update:  31st January 2015

All of the information below reflects the tests that were carried out in July 2014.  The prices are also as Microsoft published them in July 2014.

For current Performance Metrics, please the current performance test results here (for both the current V11 and the new V12).

For initial impressions of the new service tiers and performance information as of July 2014, continue reading.

Key Takeaways

The rest of this page is quite long.  The following summarises the main points:

Read and Write Rates

• These performance tests have estimated the maximum read rates of each Azure SQL Database edition / tier as:  Web/Business = 550 MB per minute, Basic = 40 MB/min, Std S1 = 30 MB/min, Std S2 = 140 MB/min, Prem P1 = 400 MB/min and Prem P2 = 770 MB/min.
• These performance tests have estimated the maximum write rates of each Azure SQL Database edition / tier as:  Web/Business = 100 MB per minute, Basic = 9 MB/min, Std S1 = 19 MB/min, Std S2 = 38 MB/min, Prem P1 = 47 MB/min and Prem P2 = 90 MB/min.

The above figures are the average rates per database (i.e. total database throughput) taken from three 60 minute tests conducted against each edition / tier – they are not per connection rates.

These figures (like the rest of the test results in this series) already include the increases in performance for Standard Tier – i.e. S1 to 15 DTUs and S2 to 50 DTUs.  This increase was announced in mid-May (e.g. see post from Tobias at Microsoft in this thread) and live by early June.  The performance increase was included in another post on the Microsoft Azure blog on 8th July, however, this was merely a repeat of the earlier announcement.  The majority of these performance tests were conducted in mid to late June.

Performance of Newer Service Tiers Compared to Today’s Web / Business Edition

• Given that Microsoft are closing down the Web / Business edition next year, the relative performance of the newer service tiers (Basic, Standard and Premier) is a key consideration for many Azure SQL Database customers .
• If you have an Azure SQL database in Web/Business Edition today, and that database is above a few GB and/or has anything above “light” usage, it is likely the Standard Tier – as it is today – will not be a viable replacement once Web/Business is withdrawn.
• Compared to the Web/Business edition, the newer Standard Tier S1 and S2, have performed significantly worse in these performance tests.  E.g. S1 has only 5% of the disk read performance, S2 only 25% of the read performance of Web/Business in these tests.
• In these tests (with a 36 GB database), Standard Tier S1 and S2 reached only 0.1% and 0.2% of the transaction throughput of Business.  Your Mileage May Vary, though I suspect for anything other than a “small” database and “light” load, the pattern will be similar.
• This likely means many customers will need to move from Web/Business to Prem P1 to maintain performance, which means a likely very large cost increase, probably of the order of two to three times the cost or even more for smaller databases.  It is also worth noting that in these tests, whilst P2 generally outperformed Web/Business, P1 was a more mixed comparison, but at least of the same order of magnitude.

Bottom Line

The Good

• The new tiers do display impressive consistency (less variability) compared to the Web / Business editions.
• Premier P2 (and presumably P3) generally significantly outperform Web / Business.

The Bad

• Even though Web / Business is less consistent, on average it significantly outperforms Basic, Standard S1 and Standard S2.
• This makes the new service tiers, particularly Standard, disappointing.

The Ugly

• The newer tiers are even more disappointing once cost is taken into account – i.e. worse performance at significantly higher cost than Web / Business:

The Painful

• The Price-Performance Point can be quantified and compared using the “Price per (MB/min)” of each edition or service tier.
• Storage performance is of course by no means the only factor affecting performance but, at the moment, in Azure SQL Database, it is a very dominant factor –  especially when considering the move from Web/Business into the new Service Tiers.
• Starting with read-rate price-performance:

• The write-rate price-performance comparison is even more painful:

• These price-performance figures are eye-watering.  Almost all compare very badly with Web/Business.  Price-performance does not increase linearly.  It becomes increasingly more expensive to obtain higher throughput rates.

Microsoft’s Azure Sql Benchmark (ASB), Database Throughput Units (DTU) and Costs

• It is also interesting that, in comparison to these tests, Microsoft’s own DTU benchmarks against S1 and S2 were made with only 750 MB and 3.6 GB databases respectively (see details here).  That Microsoft are testing with such small databases in Standard Tier despite it’s 250 GB capacity seems a little odd.
• For a 750 MB database, Web/Business cost = 10 USD per month, S1 = 40 USD.
• For a 3.6 GB database, Web/Business = 22 USD per month, S2 = 200 USD per month.
• And remember, this is for, on average, according to these tests, a service tier with significantly less resources.

Standard Tier Footnote

• S1 has performed very poorly in these performance tests.  I would be very hesitant to place a workload on S1 where the database size is above a few GB or where the load is anything above very light.  S1 provides an initial read “burst” but after that a sustained read rate of only 5% of current web/business.  If that initial burst fails to get most of your frequently accessed data into the buffer pool, then performance can be substantially dragged back by that very low read rate.
• If your current Azure workload uses Web/Business reasonably heavily, then it is likely S2 will also be insufficient and Prem P1 at a minimum will be required…

PS…

• The new service tiers are still at the Preview Stage.  They haven’t yet reached General Availability.
• Microsoft have suggested that the performance of the new tiers is not yet fixed and may be adjusted.
• I suspect, once Microsoft start to receive more “loud and clear” feedback, the price-performance point (which at the moment seems way-off for Standard Tier) will be improved.

For more details on the points above, please continue reading below.

Summary of Test Results and Metrics

MB Per Minute Rates

These rates roughly estimate the maximum each edition / service tier is capable of.  For the newer service tiers, these rates were generally measured when the quota usage rates (as reported in sys.resource_stats) were at 100%.  For Web / Business, a series of tests were generally conducted utilising various thread counts to establish maximum rates.

For more details about the individual tests, see the other posts in this series (the Test lines in the tables below link back to the relevant post for that column).

In these tables, I believe the Log Write and Disk Read rates are reasonably representative (due to the simple nature of the tests).  The example workload is just that – one possible workload, so these figures will vary by workload.

For comparison purposes, the official DTU allowances per tier at the time of these tests were:
Basic=5, S1=15, S2=50, P1=100 and P2=200.  These tests were conducted throughout June, well after the mid-May announcement of the Basic and Standard Edition DTU increases.  The Scale Tests (in the Read Ltd Example Workload column) were conducted in late June, over a month after the DTU increases.

Comparison to Web / Business Edition %

The table shows the same figures as above, expressing them as an approximate percentage of the performance of Web / Business edition.  This allows easier comparison between Web / Business and the newer service tiers.

Notes (on the above data tables)

Example Workload

The example workload is 20% single row inserts, 30% single row updates, 30% selects and 20% single row deletes.  Of the selects, 75% are single row selects (so called Direct Selects) and 25% are multiple row selects (so called Indirect Selects).  All the single row operations were performed via stored procedure, selecting rows via the clustered index key.  The multiple row selects covered small ranges of the clustered index (average 660 rows) and executed via text SQL.  For more details, see the Scale Tests post.

Example Workload Rates

For the example workload, “Not Read Ltd” refers to a test where all data was already in the buffer pool at the start of the test (i.e. generally not normal so something of a special case) and thus read rates were not limited by the disk read quota (see details here).  “Read Ltd” refers to a test where none of the data was in the buffer pool at the start of the test and thus the test was subject to / performance typically limited by the physical disk read quota limit of the service tier (see details here).  Obviously, some data is loaded into the buffer pool during the tests – each of which was two hours long.  These “Read Ltd” figures are an average over the two hours.

The comparison % figures (and CTUs) have been adjusted to take account of the number of threads across the different tests and thus compare like-for-like.

CTUs

CTUs were defined, half jokingly, in the previous Scale Tests post (Chris’s Throughput Units).  They accurately reflect my test results, but are not proposed as a serious representative benchmark figure.

Basic Edition

The example workload for Basic edition was obviously conducted against a 2 GB DB, the maximum permitted size for that service tier.

Headlines

Headline:  Good Consistency

The new service tiers have generally shown consistent performance in these performance tests, generally providing their maximum rates substantially more consistently than observed in Web / Business edition.  This was one of the stated aims of the new service tiers and Microsoft have largely achieved it.

The only area where some moderate inconsistency was shown in the new service tiers was in the physical disk read rates.  The preview is still running, so Microsoft have time to work on this.

Headline:  Low Disk Read Quota Allowances, Poor Performance of Standard Edition

The Scale Tests conducted in this series of performance tests have demonstrated just how poorly Standard Edition can perform if it is read-constrained.  By contrast, the non-read limited performance figures actually compare favourably to Web / Business.

The poor performance when read limited is not surprising when the average sustained sequential read performance figures (from the Sequential Selects test) are considered.  According to these results, S1 has only 5% of the read performance of Web /Business.  S2 has a little more at 25%, but this will, I suspect, hardly make S2  a viable replacement in many cases for Web / Business.  And that means P1 is needed – with still only 70% of the read rate of Web / Business but at least roughly the same order of magnitude.  BUT: moving from Web/Business to P1 = a substantial cost increase.

Again Microsoft still have time to re-balance these things as the preview runs on.

Headline:  Great P2+ Performance

The new P2 tier (tested as part of these tests) performed extremely well compared to Web / Business.  P3 is presumably even better but was not tested in these tests (too costly for this piece of work).

Headline:  Premium Tier Pushes Both Upwards AND Downwards

I would speculate that the introduction of the P2 and P3 tiers has had two effects.

First, quite obviously, at the top end of Premium, it means SQL Database capabilities have pushed higher than ever before.

Secondly, and perhaps a little less obviously, it means the bottom end of Premium has pushed downwards and reduced the capabilities of Standard.  The measured rates for disk read and log write of P1 are both somewhat below Web / Business – and that pushes the capabilities of Standard further down.  Of course, we know performance of Web / Business is more variable but still, these average figures are quite clear.

Scale Test Conclusions

[ The text in this section is reproduced from the previous post].

The Scale Tests (“Example Workload – Read Ltd” columns in the tables above) have illustrated how one example workload performs across the different editions, tiers and performance levels of SQL Database.  In particular, how the nature of the workload can generate differences in performance vastly different to simple expectation based on the DTU figures for the different tiers and performance levels.

I have created the CTUs (Chris’s Throughput Units) in these tests half jokingly – actually, 90% jokingly, even though they do accurately reflect my test results.  I don’t claim for a second they are any more useful than those figures provided by Microsoft.  Microsoft have undoubtedly put substantially more effort into creating a rate that much better reflects more workloads and scales.

My point really is that DTUs, CTUs or whatever else aren’t a reliable guide to planning / design.  They are perhaps a very rough starting point but SQL Server performance doesn’t scale nicely and simply according to one simple blended benchmark number.

Some basic read and write rates can be helpful though.  By the way – the CTU benchmark comes supplied with these figures in contrast to other xTU style benchmarks that may be available!  Though there is a disclaimer the CTU rates are estimates of course, not official, subject to change, etc!

The CTU benchmark also compares the new service tiers to Web/Business, though again it is noted that Web/Business performance is more variable.  That still doesn’t stop averages being captured nor does it mean readers aren’t intelligent enough to understand that any Web/Business average has a wider standard deviation than for the new Service Tiers.

These tests have also illustrated how read rate appears to quite heavily dominate SQL Database performance under the new service tiers.  And how life is very unpleasant when that quota is running short.  Of course, I could have reduced the fragmentation in the test database, used a more optimum design, etc.  But, just in case you missed it everywhere else I said it, I don’t claim this is the best workload to benchmark with.  Though I am sure that there are plenty of other databases in Web/Business today with similar or bigger problems – that as of today are running perfectly/moderately happily (just like this workload did in Web/Business) – whose owners may be in for a surprise when they try running in an environment that is constrained on read rate.  For these databases, Standard Edition as of today is probably not going to be a happy place to be.

Which neatly brings me to one aspect I haven’t mentioned thus far.  Cost.  For this workload, clearly Premier P1 would be needed.  That’s would represent a cost change from 65 UKP (100 USD) to 295 UKP (465 USD) per month, i.e. a multiple of 4.6!  (And that’s just the preview pricing!)  So, that database that is running happily today in Web/Business (despite its “issues”) is going to potentially cost quite a lot more this time next year to keep running…

Which leaves me all the way back with my original thought I posted way back in April/May time – that these new Service Tiers actually represent quite an increase in the cost of SQL Database.  Even a 150 GB database (i.e. at the top end of SQL Database per GB pricing in Business Edition today) will cost twice as much to run in P1 in future (and again, that’s just the preview pricing, GA  pricing is four times today’s price!).

Finally, a couple of other footnotes comparing Web/Business to the new service tiers:

• The sys.resource_stats DMV recently appeared in the Web/Business edition, presumably to allow comparison between the existing tiers and the new tiers.
• Firstly, from these tests, we have seen that 100% values in the log write and physical disk read columns for Web/Business actually cover a VERY wide range of actual performance levels, often multiples of 3 or 4 or even 5.  So, if you are seeing 100%, that actually doesn’t tell you the true maximum.
• I believe I read in a blog post that the read and write percentages in this DMV in Web/Business have been set at half S2.  I.e. 100% Business = half S2.  Or equivalently, S2 has twice the performance of Business.  From these tests, the opposite actually appears to be true.  E.g. the physical read read rate measured for S2 = 140 to 210 MB per minute, for Business = 550 MB per minute.

This conclusion has turned into something of a stream of conciousness.  It may sound overly critical.  Actually, I do really like the concepts behind the changes Microsoft is bringing to SQL Database.  They bring an increased level of robustness and probably should have been the way SQL Database was designed originally.  There are many good solid technical enhancements.  In practice, P2 significantly outperforms Web/Business in almost all tests (P1 is a more mixed picture).  So my real concerns are about the transition from Web/Business and what appears from here to be quite significant associated cost increases in order to maintain the same performance.  Standard Edition in particular seems a “somewhat” poor relation to Web/Business due to the constrained read quota.  And, great though Premium is, it costs significantly more.

Is this the equivalent in aviation terms of moving from a rough around the edges, sometimes unreliable but generally quite high performing economy class only Budget Airline to a National/Flag Carrier with it’s multiple cabins and seating classes, all generally more expensive than the budget carrier?

As a PS, I should remind that these tests were conducted in the Western Europe data centre.  It is conceivable that there is plenty of spare Web/Business capacity here (that may not exist in other Azure data centres worldwide) so Web/Business may perform better here than elsewhere.  If that is the case, the general conclusions here would be less widely applicable.  Time and the experiences of others will certainly tell if that is the case or if these conclusions are generally applicable…

Next Steps

If I was to continue, I would think about some of the following, in no particular order (there is always more to investigate but you need to stop / take a break at some point):

• Try a more optimally designed workload in Standard Edition.
• Gradually turn up the size of the database and/or request rate in Standard Edition.
• Run some tests with indexes – though building, reorganizing, rebuilding, etc indexes is painful in Standard Edition.
• Gather more detailed IO stats, e.g. from the DMVs during tests. I have a newer version of the Cloud Service that now collects DMV IO metrics through tests but alas this study has come to an end for now.  I may use this in the future.
• Change the distribution function to less favour the buffer pool (i.e. reduce the Premium tier advantage).
• Run some tests with no IndirectSelects as they can totally destroy Standard Edition.
• The new service tiers have shown good consistency (which was something I wanted to validate), there is less need to run each test three times.  Twice or even once only is probably sufficient.
• Re-run some of these tests once the new Service Tiers hit GA.

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

Scale 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 these tests are not limited to a specified request rate.

The earlier tests only ran on a relatively small set of data in memory.  These tests run against a newly restored and much larger 36 GB database (except for Basic edition, where a 2 GB database was used).  The size of the database and the fact it is newly restored means that none of the data exists in the buffer pool at the start of the test and the test therefore includes the effect of read rates from physical disk for each tier.

Using the same workload against each tier is, on one hand, not a smart thing to do – i.e. clearly we expect the lower tiers/performance levels to not perform as well as the top end.  On the other hand, running the same workload is a handy way to illustrate exactly what those differences are like.

The previous Insert, Select, Update and Delete tests randomly picked a row from any point in the database with equal probability.  Since those tests were working with data entirely in the buffer pool, that was fine.  In most databases, certain data is more likely to be hit than other data, e.g. typically more recent data is more “active”.

To at least loosely replicate that, two distribution functions were used in these tests that meant the data towards the end (highest row ID values) of each test table was more likely to be accessed than data at the start.  The first distribution function, used for the single row operations (i.e. Select Direct, Update and Delete) had a high probability of choosing data in the last 5% of each table.  The second distribution function used for row ranges (i.e. Select Indirect) had a slightly higher probability of selecting a slightly older data – i.e. the function was less peaked at the end of the table.

Probability distribution charts for these functions can be found in an appendix at the end of this post.

I am not at all claiming that this workload and these distributions are representative of the majority of databases in use.  They are merely one example workload and data access pattern.  The single row distribution function is in particular quite “peaked”, however the choice was deliberate – having such a peak should allow some data to be relatively quickly cached in the buffer pool.

Each test lasted 120 minutes and was executed three times against each tier.  Tests were performed against Business Edition with 1 thread generating requests, 2 threads, 4 threads and 8 threads.  This is due to the opportunistic nature of that edition (which means more threads = more throughput, to some extent).  This is less true of the new service tiers, where the different quotas come into play and additional threads don’t improve performance once a quota limit is reached (which generally happens in the tests here).

More details of the test parameters and configurations can be found in the appendices at the end of this post.

Test Results

Overview

Results from the 30 Scale Tests are shown in the 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.

Clearly the Standard Edition has not performed well under this workload.  Business 4T outperforms P1 4T.  Prem P2 8T outperforms Business 8T.  Both of these comparisons are like-for-like in terms of Cloud Service configuration (i.e. both 4T tests were executed from an A2 instance and both 8T tests from an A3 instance).

Plotting the same data on a logarithmic scale:

P1 has outperformed S2 by a factor of around 100.  P1 has outperformed S1 by a factor of around 1000.  Basic has performed well, though it should be remembered that the Basic Edition test was conducted against a 2 GB database, whilst all the other tests were performed against a 36 GB database.  Still, this difference in performance is interesting.

Test Profiles

The charts below show the performance profiles over the lifetime of the tests in the charts above. Each test has been plotted on a separate chart to allow throughput and load % to be easily compared.  The first test of the three on each tier (i.e. ST1) is plotted and discussed below.  Charts for the ST2 and ST3 tests are generally similar and presented in an appendix at the bottom of this post.

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.  There also appeared to be a bug or infrastructure fault occurring during these tests that was prevent SQL Database capturing data for every five minute period – the gaps appear to follow quite a regular pattern but fortunately don’t prevent the general trends from being observed.

Basic

After a five to ten minute warm-up period performance quickly reaches a good level.  We can see the read rate decreasing over the course of the test as more of the database appears to be cached.  This result is rather in contrast to all of the other results below and so should be considered as something of a special case.

For the majority of this test, performance is limited by the log write quota.

Response times are good after the first couple of minutes.  Since this database is write-limited, unsurprisingly the Insert, Update and Delete operations take the most time.  Again, this is something of a special case compared to the other tests.

 Std S1

This is not a pretty profile, at all.   Performance is terrible at the start and only improves, perhaps very slightly, at the very end of the test.   The test is read constrained throughout.

There were never more than two requests pending at any one time (the test was run with two threads).  The chart below shows the average response times.  Actually, since so few requests were processed in this test, each data point generally represents an individual request:

There are so few requests processed in this test, we can easily list them, by start time.  Here are all 37 requests from the first hour:

“Byte” values above represent the length of the rows sent to SQL Database (for inserts, updates) or the lengths of the rows read from SQL Database (for selects).

It would be interesting to see just how many pages SQL Server is processing for each of these requests.  In theory, it should be relatively low, since all requests search by the clustered index key – i.e. WHERE RowId = … for the SelectDirect, Update and Delete requests, or WHERE RowId between… for the SelectIndirect requests (which cover an average range of 660 rows).

The database was not defragmented after importing which certainly won’t help performance – the bacpac import process tends to leave clustered indexes relatively fragmented, e.g. these tables typically had over 75% fragmentation (yet another reason to dislike bacpacs).

It is interesting to note that later in the test, after minute 90, generally all of the request types except SelectIndirect respond sub-second.  But those SelectIndirects are still performing very badly and contributing most to the poor performance.

Clearly, being read limited is not a happy place to be.

The second test shows a similar pattern:

The improvement in performance after 90 minutes in all but the SelectIndirect requests is easier to see in this test.  Perhaps given a test over 24 hours performance would have continued to improve further, though the S2 result below suggests that is probably unlikely.

Std S2

The chart clearly shows a higher throughput compared to S1.  Again the test is read constrained throughout.

A stable state of performance is reached after around 35 minutes, vs. 90 minutes for S1.  Again, the SelectIndirect requests have the slowest response.  Response times for all request types are roughly an order of magnitude better than S1.

Prem P1

This clearly shows a database performing much more efficiently than S2.  The database reaches a reasonably stable level of performance after only ten minutes.  After another 30 minutes a significant proportion of the database workload has been cached (judging by the reduction in physical disk utilisation) and performance has increased by another order of magnitude.

Throughput in the stable state is roughly 100 times that of S2 (for what is in effect only a doubling of DTUs between S2 and P1).

This is the first of the new tiers able to operate this database workload without being either  read or write limited.

The response time comparison to S2 is interesting.  The average response time for the single row operations is the same order of magnitude (P1 perhaps half that of S2).  However, the multiple row SelectIndirect operation response time is roughly two to three orders of magnitude (i.e. 100 to 1000 times) better.  Clearly, a higher read quota has brought about a huge benefit.

There also appears to be a regular peaks in performance (e.g. the response time dips at roughly 58, 68, 77, 87, 97 minutes).  In the Select Sequential post I speculate that read quota may be allocated in blocks per unit of time.  Perhaps these dips represent additional “premium performance” quota being allocated, then quickly consumed (giving a read rate boost) before read performance defaults back to a lower level for the tier.

For this workload, P1 would clearly be the optimum service tier and performance level choice based on a balance of cost and performance.

Prem P2

On P2 the workload is working efficiently in around only five minutes.  The database is read limited for less than five minutes.  Throughput is around three to four times that seen in P1, for again what is only a doubling of the DTUs.

Response times are probably half that seen in S1.  Again the same pattern of periodic peaks in performance (i.e. dips in response time) are apparent, again at intervals of around ten minutes.

Business 1T

It is interesting to note the obvious dip here around minute 20 – an example of the variability in performance that can be encountered with this edition.  We are reported to be write limited, though we know from the earlier tests this limit means little in practice for this edition.

Response times are very good.  It is interesting to note that the SelectIndirect requests in this edition do not have a response time that is noticeably longer than the other request types.  This is in comparison to the newer service tiers where this part of the workload performed significantly worse throughout.  This suggests read performance is noticeably better in Web/Business than the newer service tiers.

Business 2T

Very similar to the 1T test.

Business 4T

Again, very similar.  With a noticeable blip around minute 70.

Business 8T

With eight threads, we reach a performance that is comparable to P2 (that test also ran with eight threads).

Scale Test Conclusions

The Scale Tests have illustrated how one example workload performs across the different editions, tiers and performance levels of SQL Database.  In particular, how the nature of the workload can generate differences in performance vastly different to simple expectation based on the DTU figures for the different tiers and performance levels.

From the figures gathered in these tests, we can create yet another benchmark figure (CTU, Chris’s Throughput Units, for this rough test database workload here) as follows:

Now, I have put the CTUs here half jokingly (actually, 90% jokingly, even though they do accurately reflect my test results).  I don’t claim for a second they are any more useful than those figures provided by Microsoft.  Microsoft have undoubtedly put substantially more effort into creating a rate that much better reflects more workloads and scales.

My point really is that DTUs, CTUs or whatever else aren’t a reliable guide to planning / design.  They are perhaps a very rough starting point but SQL Server performance doesn’t scale nicely and simply according to one simple blended benchmark number.

Some basic read and write rates can be helpful though.  By the way – the CTU benchmark comes supplied with these figures in contrast to other xTU style benchmarks that may be available!  Though there is a disclaimer the CTU rates are estimates of course, not official, subject to change, etc!  These figures can be found summarised in the next post and individually in the earlier posts about each test type.

The CTU benchmark also compares the new service tiers to Web/Business, though again it is noted that Web/Business performance is more variable.  That still doesn’t stop averages being captured nor does it mean readers aren’t intelligent enough to understand that any Web/Business average has a wider standard deviation than for the new Service Tiers.

These tests have also illustrated how read rate appears to quite heavily dominate SQL Database performance under the new service tiers.  And how life is very unpleasant when that quota is running short.  Of course, I could have reduced the fragmentation in the test database, used a more optimum design, etc.  But, just in case you missed it everywhere else I said it, I don’t claim this is the best workload to benchmark with.  Though I am sure that there are plenty of other databases in Web/Business today with similar or bigger problems – that as of today are running perfectly/moderately happily (just like this workload did in Web/Business) – whose owners may be in for a surprise when they try running in an environment that is constrained on read rate.  For these databases, Standard Edition as of today is not going to be a happy place to be.

Which neatly brings me to one aspect I haven’t mentioned thus far.  Cost.  For this workload, clearly Premier P1 would be needed.  That’s would represent a cost change from 65 UKP (100 USD) to 295 UKP (465 USD) per month, i.e. a multiple of 4.6!  (And that’s just the preview pricing!)  So, that database that is running happily today in Web/Business (despite its “issues”) is going to potentially cost quite a lot more this time next year to keep running…

Which leaves me all the way back with my original thought I posted way back in April/May time – that these new Service Tiers actually represent quite an increase in the cost of SQL Database.  Even a 150 GB database (i.e. at the top end of SQL Database per GB pricing in Business Edition today) will cost twice as much to run in P1 in future (and again, that’s just the preview pricing, GA  pricing is four times today’s price!).

Finally, a couple of other footnotes comparing Web/Business to the new service tiers:

• The sys.resource_stats DMV recently appeared in the Web/Business edition, presumably to allow comparison between the existing tiers and the new tiers.
• Firstly, from these tests, we have seen that 100% values in the log write and physical disk disk columns actually cover a VERY wide range of actual performance levels, often multiples of 3 or 4 or even 5.  So, if you are seeing 100%, that actually doesn’t tell you the true maximum.
• I believe I read in a blog post that the read and write percentages in this DMV in Web/Business have been set at half S2.  I.e. 100% Business = half S2.  Or equivalently, S2 has twice the performance of Business.  From these tests, the opposite actually appears to be true.  E.g. the average physical read read rate measured for S2 = 210 MB per minute, for Business = 550 MB per minute.

This conclusion has turned into something of a stream of conciousness.  It may sound overly critical.  Actually, I do really like the concepts behind the changes Microsoft is bringing to SQL Database.  They bring an increased level of robustness and probably should have been the way SQL Database was designed originally.  There are many good solid technical enhancements.  In practice, P2 significantly outperforms Web/Business in almost all tests (P1 is a more mixed picture).  So my real concerns are about the transition from Web/Business and what appears from here to be quite significant associated cost increases in order to maintain the same performance.  Standard Edition in particular seems a “somewhat” poor relation to Web/Business due to the constrained read quota.  And, great though Premium is, it costs significantly more.

Is this the equivalent in aviation terms of moving from a rough around the edges, sometimes unreliable but generally quite high performing economy class only Budget Airline to a National/Flag Carrier with it’s multiple cabins and seating classes, all generally more expensive than the budget carrier?

As a PS, I should remind that these tests were conducted in the Western Europe data centre.  It is conceivable that there is plenty of spare Web/Business capacity here (that may not exist in other Azure data centres worldwide) so Web/Business may perform better here than elsewhere.  If that is the case, the general conclusions here would be less widely applicable.  Time and the experiences of others will certainly tell if that is the case or if these conclusions are generally applicable…

Appendix – Test Configuration

Appendix – Test Results

Appendix – Row Selection Probability Distribution Functions

The chart below illustrates the probability distribution functions used for selecting single rows (for the Direct Select, Update and Delete operations) and for selecting multiple rows (for the Select Indirect operation).  The x-axis represents a theoretical table with 1000 rows.  The y axis represents the probability of each row being accessed for a single operation.

Zooming into the lower portion of the chart:

In practice, this meant a data access operation had the following probabilities of hitting different regions within a table (again, using a table of 1000 rows as an example):

So, for example, a single row select (Select Direct) request had approximately 50% chance of hitting the last 5% of the table (i.e. between rows 950 and 1000).  The probability that a multiple row select (Select Indirect) would hit that part of the table is approximately 30%.

Appendix – ST2 and ST3 Charts

The main text above includes only the performance profile for the first of the three scale tests conducted against each tier / edition (to avoid overloading the main text with even more charts than are already present!).  The performance profiles for the second and third tests against each tier are included below for those that really love this kind of thing.

Generally speaking, the main patterns of behaviour are consistent within each performance level / tier / edition.

Basic

Std S1

Std S2

Prem P1

Prem P2

Business 1T

Business 2T

Business 4T

Business 8T

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.

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:

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

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

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

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

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

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

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

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.

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

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.

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:

For a sustained read:

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

Appendix – Test Results

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.

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

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

Business

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

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

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

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

Appendix – UT Test Results

Appendix – LT Test Configuration

Appendix – LT Test Results

The Requests per Second maximum applies in total, not per thread.

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

Combined Inserts, Direct Selects, Updates and Deletes Test Overview

This test combines the activities of the earlier Inserts and Deletes, Direct 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%
• Direct Selects – 30%
• Updates – 30%
• Deletes – 20%

As in those earlier tests, the worker threads in the UT tests are not limited to a specified request rate.  The LT tests were not conducted for this workload.

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.

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

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.

It is interesting to note here that with this workload there is a more pronounced gap between the S2 and P1 tiers, which wasn’t always seen in some of the earlier tests.

I will demonstrate below that all of the test results for the new service tiers shown above are limited by the SQL Server Log Write Rate limit, i.e. this is the determining factor in the performance.

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

Peformance of the new tiers is very consistent – other than some variation in the S2 tests. The charts show that these tests weren’t constrained on CPU but were clearly constrained on log write quota. All of the data was already present in the buffer pool during these tests – i.e. physical disk read quota had no impact.

Std2 to P2 Tiers

Business

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.

In addition, the Log Write profile looks suspiciously odd, being at 100% for all data points in all tests.  As we found in the other LT tests, this value is rather meaningless.

Combined Inserts, Direct 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 selects, updates and deletes (of average row size 400 bytes), performance of the new S2 / P1 tiers generally equals the current Business Edition. P2 significantly outperforms Business for this workload.

Appendix – UT Test Configuration

Appendix – UT Test Results

The Requests per Second maximum applies in total, not per thread.

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

Insert and Delete Test Overview

The Inserts test involves calling two different stored procedures – one to insert rows into a test table, another to delete rows.

The Cloud Service fires up a number of worker threads as specified in the test definition. The worker threads randomly decide whether to insert or delete (with equal probability).

For an insert, the worker thread generates 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).

For a delete, the 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 delete 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 a 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 deleting data that is not always resident in the buffer pool.

UT Test Results

Overview

Results from the 30 UT Inserts and Deletes 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.

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 / P1 generally equals or outperforms Business Edition.

I will demonstrate below that all of the test results for the new service tiers shown above are limited by the SQL Server Log Write Rate limit, i.e. this is the determining factor in the performance.

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

Peformance of the new tiers is very consistent – other than some variation in the S2 tests. The charts show that these tests weren’t constrained on CPU but were clearly constrained on log write quota. All of the data was already present in the buffer pool during these tests – i.e. physical disk read quota had no impact.

Std2 to P2 Tiers

Business

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.

In addition, the Log Write profile looks suspiciously odd, being at 100% for all data points in all tests. More on this below.

LT Test Results

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

Basic Tier

The chart below illustrates how the performance of the Basic Tier varies as the workload (number of requests per second) is increased. A 30 minute test was conducted at each request rate.

As the request rate is increased, the CPU utilisation and log write utilisation increase. At around 70 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

This chart shows a quite mixed set of test results.  The initial tests, at low request rates, were impeded a little (but probably to no significant effect) by having to load some of the test data from disk (despite it having been created only a few minutes earlier).  As the request rate climbed in later tests, a significant percentage of requests were being skipped, despite not reaching the disk write limit.  A period of lower database performance seems the most likely cause.

The log write limit for this workload is reached at around 140 requests per second, consistent with the earlier UT test.

Web Edition

This chart shows good consistency from Web Edition, with linear performance up to 110 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 120 requests per second – before we actually see a significant number of requests failing to be processed.

Insert and Delete Test Conclusions

The Insert and Delete Tests have shown that, for a workload of purely stored procedure based row-by-row inserts and deletes (of average row size 400 bytes), performance of the new S2 / P1 tiers generally equals or exceeds the current Business Edition. P2 significantly outperforms Business for this workload.

Appendix – UT Test Configuration

Appendix – UT Test Results

Appendix – LT Test Configuration

Appendix – LT Test Results

The Requests per Second maximum applies in total, not per thread.

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.

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.

From these results S2 generally equals or outperforms Business Edition. Interestingly, for this workload, the gap between S2 and P1 is not all that significant, but P2 is substantially better than P1 and towers above Business Edition.

I will demonstrate below that all of the test results for the new service tiers shown above are limited by the SQL Server Log Write Rate limit, i.e. this is the determining factor in the performance.

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

Basic to P1 Tiers

Peformance of the new tiers is very consistent – other than a blip towards the end of one of the S2 tests. The charts show that these tests weren’t constrained on CPU but were clearly constrained on log write quota.  All of the data was already present in the buffer pool during these tests – i.e. physical disk read quota had no impact.

Std2 to P2 Tiers

P2 follows the same pattern as the previously discussed tiers.

Business

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.

In addition, the Log Write profile looks suspiciously odd, being at 100% for all data points in all tests. More on this below.

LT Test Results

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

Basic Tier

The chart below illustrates how the performance of the Basic Tier varies as the workload (number of requests per second) is increased. A 30 minute test was conducted at each request rate.

As the request rate is increased, the CPU utilisation and log write utilisation increase. At around 60 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

This chart looks very different to the previous Basic Edition chart. Despite these tests always starting only a few minutes after the test data was created, the data is no longer resident in the buffer pool.  All of these tests were constrained by the physical disk read rate. The maximum rate of 65 or so rows per second is considerably less than the 110 rows per second seen in the S1 UT test.

Web Edition

The web edition is slightly less consistent – i.e. we start to miss requests in a more rough and ready manner.

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 more than three times this “maximum” – i.e. to around 130 or 140 requests per second – before we actually see a significant number of requests failing to be processed.

Update Test Conclusions

The UpdateTests have shown that, for a workload of purely stored procedure based row-by-row updates (of average row size 400 bytes), performance of the new S2 / P1 tiers generally equals or exceeds the current Business Edition. P2 significantly outperforms Business for this workload.

The tests have also hinted at the impact limited physical disk read quota can have on performance.  This is explored in the later Sequential Select and Scale Tests.

Appendix – UT Test Configuration

Appendix – UT Test Results

Appendix – LT Test Configuration

Appendix – LT Test Results

The Requests per Second maximum applies in total, not per thread.

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.

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

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

Business

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

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

Appendix – UT Test Results

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.

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

Reading multiple rows per request is a CPU intensive activity.  We can see the results scale roughly by the number of threads reading data (remember, this is for data already in the buffer pool, disk is not involved).  The Business 8T test ran on an A3 VM, the Prem P2 8T test ran on a A2 VM.  The A2 box was effectively max’d out on CPU, which is why the Business 8T result looks so much greater.

These charts suggest that there are no significant performance limits in place that restrict the rate at which data in the buffer pool can be read.  Even Basic edition manages 10,000 rows per second.

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

Basic to P1 Tiers