How does minimum capacity affect resource limits in serverless Azure SQL?

Ben Outram 20 Reputation points
2023-03-01T13:26:13.9066667+00:00

Hello,

I'm looking at specifying Azure SQL single databases using the vCore purchasing model, specifically standard-series Gen5 serverless.

This article on resource limits shows that each compute size specified in terms of vCore capacity has an associated minimum capacity. For example, GP_S_Gen5_10 states minimum vCore is 1.25.

Minimum capacity, apart from being bounded by the limits associated with capacity, otherwise appears to be flexible. For example, I can specify a GP_S_Gen5_10 serverless database and then in Azure Portal drag the min slider anywhere between 1.25 and 10.

My question is, what's the effect on resource limits of specifying a serverless database with a higher minimum capacity than required, i.e. outside the documented min/max configurations, e.g. GP_S_Gen5_10 with minimum capacity 4 rather than 1.25?

Is there any benefit of setting minimum capacity higher than the documented minimums required?

I would be expecting worse performance with a lower minimum capacity, since a lower minimum memory should mean that more memory is reclaimed, the database buffer cache is smaller and more queries need to retrieve data from storage. Also I'd expect that with a lower minimum capacity, there'd potentially be more scaling operations and if load balancing is required to satisfy that demand then scaling won't be instantaneous and requests could end up taking longer?

I was unable to verify that increasing that minimum capacity is having an impact on performance and resource limits.

I did a test on a serverless database, altering scale and then querying the sys.dm_user_db_resource_governance view.

I set max vCores at 10, min vCores 1.25, and queried the resource governance view. Then on the same database, adjusted min vCores to 4, leaving max vCores at 10, and waited for that scaling operation to complete. The resource governance view gave exactly the same result with identical values of min_cpu, min_memory, primary_group_min_io, primary_group_min_cpu. In fact, the date and time of the last reconfiguration last_updated_date_utc didn't change.

I repeated this test at other capacities e.g. comparing 0.5-4 to 1.25-4 and got the same result.

The settings only updated when altering max vCores, where slo_name updates too. I don't see any difference in values when only adjusting the minimum capacity setting.

Thank you.

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 32,791 Reputation points MVP
    2023-03-01T14:31:36.6633333+00:00

    The minimum capacity affects billing and not performance. It is memory and not CPU the biggest performance factor with serverless as memory is reclaimed on serverless on more aggressive way compared with other Azure SQL flavors, and you cannot control that memory behavior. Query waits are more related to memory on Serverless tier than with anything else.


  2. GeethaThatipatri-MSFT 27,002 Reputation points Microsoft Employee
    2023-03-01T18:38:54.05+00:00

    Hi, @Ben Outram Welcome to the Microsoft Q&A platform, thanks for posting the question.

    The min vcore configuration for serverless specifies the lower bound of the compute auto-scaling range.  The higher the min vcore config, the more resources that will be continuously available independent of usage.  So, this setting helps to reduce the performance impact from compute warm-up during scale-up.  To clarify, the min-max vcore config is not only an index into the min-max scaling limits for CPU, but also for memory including caches like the buffer pool for data pages and precompiled query plans.  And it’s the min memory limit for these caches that generally provides the greatest benefit in reducing compute warm-up impact.

    The resource governance settings in the DMV do not change during auto-scaling.  For example, memory scale-up is unthrottled up to the max memory limit which doesn’t change.  Memory scale-down in serverless is triggered during low or idle usage periods (unlike provisioned compute), but does not drop below the min memory limit which also doesn’t change.  Memory reclamation occurs through an alternative internal mechanism by reducing memory targets to induce memory pressure.  Cache evictions are optimized by generally selecting cache entries that are the least costly to rehydrate in terms of perf impact.  Nevertheless, there is still a price-perf trade-off in serverless due to memory reclamation and the min memory config provides some control over that impact. 

    Regards

    Geetha

    0 comments No comments