public static HyperScaleTier GetServiceObjective(HyperScaleTier currentSLO, SearchDirection direction)
{
var targetSLO = currentSLO;
var availableSlos = HyperscaleSLOs[currentSLO.Generation];
var index = availableSlos.IndexOf(currentSLO.ToString());
if (direction == SearchDirection.Next && index < availableSlos.Count)
{
targetSLO = HyperScaleTier.Parse(availableSlos[index + 1]);
}
if (direction == SearchDirection.Previous && index > 0)
{
targetSLO = HyperScaleTier.Parse(availableSlos[index - 1]);
}
return(targetSLO);
}
public static void Run([TimerTrigger("*/5 * * * * *")] TimerInfo timer, ILogger log)
{
var autoscalerConfig = new AutoScalerConfiguration();
string connectionString = Environment.GetEnvironmentVariable("AzureSQLConnection");
string databaseName = (new SqlConnectionStringBuilder(connectionString)).InitialCatalog;
using (var conn = new SqlConnection(connectionString))
{
// Get usage data
var followingRows = autoscalerConfig.RequiredDataPoints - 1;
var usageInfo = conn.QuerySingleOrDefault <UsageInfo>($@"
select top (1)
[end_time] as [TimeStamp],
databasepropertyex(db_name(), 'ServiceObjective') as ServiceObjective,
[avg_cpu_percent] as AvgCpuPercent,
avg([avg_cpu_percent]) over (order by end_time desc rows between current row and {followingRows} following) as MovingAvgCpuPercent,
count(*) over (order by end_time desc rows between current row and {followingRows} following) as DataPoints
from
sys.dm_db_resource_stats
order by
end_time desc
");
// If SLO is happening result could be null
if (usageInfo == null)
{
log.LogInformation("No information received from server.");
return;
}
// Decode current SLO
var currentSlo = HyperScaleTier.Parse(usageInfo.ServiceObjective);
var targetSlo = currentSlo;
// At least one minute of historical data is needed
if (usageInfo.DataPoints < autoscalerConfig.RequiredDataPoints)
{
log.LogInformation("Not enough data points.");
WriteMetrics(log, usageInfo, currentSlo, targetSlo);
conn.Execute("INSERT INTO [dbo].[AutoscalerMonitor] (RequestedSLO, UsageInfo) VALUES (NULL, @UsageInfo)", new { UsageInfo = JsonConvert.SerializeObject(usageInfo) });
return;
}
// Scale Up
if (usageInfo.MovingAvgCpuPercent > autoscalerConfig.HighThreshold)
{
targetSlo = GetServiceObjective(currentSlo, SearchDirection.Next);
if (targetSlo != null && currentSlo.Cores < autoscalerConfig.vCoreMax && currentSlo != targetSlo)
{
log.LogInformation($"HIGH threshold reached: scaling up to {targetSlo}");
conn.Execute($"alter database [{databaseName}] modify (service_objective = '{targetSlo}')");
}
}
// Scale Down
if (usageInfo.MovingAvgCpuPercent < autoscalerConfig.LowThreshold)
{
targetSlo = GetServiceObjective(currentSlo, SearchDirection.Previous);
if (targetSlo != null && currentSlo.Cores > autoscalerConfig.vCoreMin && currentSlo != targetSlo)
{
log.LogInformation($"LOW threshold reached: scaling down to {targetSlo}");
conn.Execute($"alter database [{databaseName}] modify (service_objective = '{targetSlo}')");
}
}
// Write current SLO to monitor table
WriteMetrics(log, usageInfo, currentSlo, targetSlo);
conn.Execute("INSERT INTO [dbo].[AutoscalerMonitor] (RequestedSLO, UsageInfo) VALUES (@RequestedSLO, @UsageInfo)", new { @RequestedSLO = targetSlo.ToString().ToUpper(), UsageInfo = JsonConvert.SerializeObject(usageInfo) });
}
}