private async Task <BestFitResult> BestFitAsync(SQLServerOverview sql, Func <ManagedInstance, float> miProp, Func <SQLServerOverview, float> sqlProp, string PropertyName)
{
var mi = mis.Where(m => miProp(m) >= sqlProp(sql) && (m.SSD == sql.SSD || !sql.SSD)).First();
string mi_family_search;
if (mi.productName.Contains("Business Critical"))
{
mi_family_search = "SQL Managed Instance Business Critical";
}
else
{
mi_family_search = "SQL Managed Instance General Purpose";
}
int requiredStorage = RoundStorageUp(sql.TotalSizeinGB);
double compute_cost = regionalManagedInstancePricing.Where(i => i.productName == mi.productName && i.skuName == mi.Cores.ToString() + " vCore").First().retailPrice * 730;
double storage_cost = regionalManagedInstancePricing.Where(i => i.productName == mi_family_search + " - Storage" && i.meterName == "Data Stored").First().retailPrice *requiredStorage;
double license_cost = regionalManagedInstancePricing.Where(i => i.productName == mi_family_search + " - SQL License").First().retailPrice * 730 * mi.Cores;
return(new BestFitResult
{
Name = mi.Name,
MIStat = miProp(mi).ToString(),
InfluencingStat = sqlProp(sql).ToString(),
StatName = PropertyName,
RecommendedMI = mi,
Recommended = false,
MonthlyComputeCost = compute_cost,
MonthlyStorageCost = storage_cost,
MonthlyLicensingCost = license_cost
});
}
public static async Task <SQLServerOverview> ProcessAsync(StreamReader source_file)
{
SQLServerOverview sql = new SQLServerOverview();
using (MemoryStream procmon_data = new MemoryStream())
{
using (var mem_write = new StreamWriter(procmon_data))
{
bool first_line = true;
while (source_file.Peek() >= 0)
{
if (first_line)
{
string header = Regex.Replace(source_file.ReadLine(), @"\\\\(.*?)\\", "\\", RegexOptions.IgnoreCase);
// Minus 2 because of 1 extra occurance for total and 1 extra item when splitting
sql.CoreCount = header.Split("% Processor Time").Length - 2;
await mem_write.WriteLineAsync(header);
first_line = false;
//Skip second row as it usually has no valid data.
await source_file.ReadLineAsync();
}
else
{
await mem_write.WriteLineAsync(source_file.ReadLine());
}
}
mem_write.Flush();
procmon_data.Seek(0, SeekOrigin.Begin);
using (var reader = new StreamReader(procmon_data))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
await csv.ReadAsync();
csv.ReadHeader();
while (await csv.ReadAsync())
{
SQLProcmonData procmon = new SQLProcmonData
{
Timestamp = csv.GetField <DateTime>(0),
BatchesPerSecond = csv.GetField <float>("\\SQLServer:SQL Statistics\\Batch Requests/sec"),
UserConnections = csv.GetField <int>("\\SQLServer:General Statistics\\User Connections"),
TotalServerMemory = csv.GetField <float>("\\SQLServer:Memory Manager\\Total Server Memory (KB)"),
TotalProcessorUsagePercent = csv.GetField <float>("\\Processor(_Total)\\% Processor Time"),
DiskReadsPerSecond = csv.GetField <float>("\\LogicalDisk(_Total)\\Disk Reads/sec"),
DiskWritesPerSecond = csv.GetField <float>("\\LogicalDisk(_Total)\\Disk Writes/sec"),
LogBytesFlushedPerSecond = csv.GetField <float>("\\SQLServer:Databases(_Total)\\Log Bytes Flushed/sec"),
TotalSize = csv.GetField <float>("\\SQLServer:Databases(_Total)\\Data File(s) Size (KB)")
};
sql.procmon.Add(procmon);
}
}
}
}
return(sql);
}
public async Task <List <BestFitResult> > FindBestFitAsync(SQLServerOverview sql)
{
List <BestFitResult> bf = new List <BestFitResult>();
regionalManagedInstancePricing = managedInstancePricing.pricings.Where(i => (i.armRegionName == sql.Region || i.armRegionName == "Global") && i.type == "Consumption").ToList();
//Core match
Task <BestFitResult> coresTask = BestFitAsync(sql, mis => mis.Cores, sql => sql.MaxUsedCores, "Cores");
// Memory match
Task <BestFitResult> memoryTask = BestFitAsync(sql, mis => mis.MaxMemory, sql => sql.MemoryAssignedinGB, "Memory");
// IOPS match
Task <BestFitResult> iopsTask = BestFitAsync(sql, mis => mis.IOPS, sql => sql.MaxTotalIops, "IOPS");
// TLog match
Task <BestFitResult> logTask = BestFitAsync(sql, mis => mis.LogWrite, sql => sql.MaxTlogBandwidthinMB, "LogWrite");
// Size match
Task <BestFitResult> sizeTask = BestFitAsync(sql, mis => mis.SizeLimit, sql => sql.TotalSizeinGB, "SizeLimit");
var results = await Task.WhenAll(coresTask, memoryTask, iopsTask, logTask, sizeTask);
foreach (var result in results)
{
bf.Add(result);
}
// Get overall recommended, which will be the "largest" MI to prevent bottlenecks
bf.Sort();
var recommendedbf = bf.Last();
recommendedbf.Recommended = true;
bf.Reverse();
return(bf);
}