Search code examples
sql-serverazureazure-sql-databaseazure-data-factoryautoscaling

SQL Azure database Scaling Up/Down takes a long from S1 to S7

as opposed to from S2 to S7

I use the following SQL query to scale Azure Sql database up/down from inside a Azure Data Factory Lookup Activity

ALTER DATABASE [@{pipeline().parameters.DatabaseName}] 
MODIFY (
  SERVICE_OBJECTIVE = '@{pipeline().parameters.NewServiceObjective}'
);

When scaling up to S7 from S2 or vice versa it takes on average between 40 seconds and 90 seconds. However, when scaling up to S7 from S1 or vice versa it takes on average between 5 and 10 minutes.

Screenshots from Activity log - I would post the results from sys.dm_operation_status but the data is only retained for approximately 1 hour.

S1 to S7 - vice versa enter image description here

S2 to S7 - vice versa enter image description here

Why this huge difference? Is there a way to scale this quicker?

This same difference is seen if the query is run on SSMS.

Solution

  • Regarding data storage, the Standard S0 and S1 service tiers are placed on Standard Page Blobs. Standard Page Blobs use hard disk drive (HDD)-based storage media and are best suited for development, testing, and other infrequently accessed workloads that are less sensitive to performance variability.

    Resource limits for single databases using the DTU purchasing model - Azure SQL Database

    So scaling from S1->S7 is a size-of-data operation.