public IActionResult GetWorkloadAnalysisDetailForBestEnvironment(GetWorkloadAnalysisDetailForBestEnvRequest request)
{
GetWorkloadAnalysisDetailForEnvReponse result = new GetWorkloadAnalysisDetailForEnvReponse();
if (request != null)
{
HandleException(() =>
{
var environmentsRepository = DALRepositories.GetVirtualEnvironmentsRepository();
var realStatementEvaluationsRepository = DALRepositories.GetWorkloadAnalysisRealStatementEvaluationsRepository();
var environmentID = environmentsRepository.GetBestEnvironmentForWorkloadAnalysis(request.WorkloadAnalysisID);
if (environmentID > 0)
{
var env = environmentsRepository.GetDetail(environmentID);
var statements = realStatementEvaluationsRepository.GetAllForWorkloadAnalysis(request.WorkloadAnalysisID);
result.Data = Converter.ConvertToWorkloadAnalysisDetailForEnv(env, statements);
}
else
{
result.Data = new WorkloadAnalysisDetailForEnvData();
}
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetOverviewStats(StatsOverviewRequest request)
{
StatsOverviewResponse result = new StatsOverviewResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new StatsOverviewData();
int topCount = 10;
var totalRelationStatsRepository = DALRepositories.GetTotalRelationStatisticsRepository();
var statementStatsRepository = DALRepositories.GetNormalizedStatementStatisticsRepository();
// alive relations
var summaryTotalRelationStatistics = totalRelationStatsRepository.GetSummaryTotalRelationStatistics(request.DatabaseID, request.Filter.DateFrom, request.Filter.DateTo, topCount);
data.MostAliveRelations = Converter.ConvertToMostAliveRelations(summaryTotalRelationStatistics);
// top executed
var topExecuted = statementStatsRepository.GetSummaryTotalStatementStatistics(request.DatabaseID, request.Filter.DateFrom, request.Filter.DateTo, DAL.Contracts.SummaryNormalizedStatementStatisticsOrderBy.ExecutionCount, null, topCount);
data.MostExecutedStatements = Converter.ConvertToMostExecutedStatements(topExecuted);
// slowest
var topSlowest = statementStatsRepository.GetSummaryTotalStatementStatistics(request.DatabaseID, request.Filter.DateFrom, request.Filter.DateTo, DAL.Contracts.SummaryNormalizedStatementStatisticsOrderBy.MaxDuration, null, topCount);
data.MostSlowestStatements = Converter.ConvertToMostSlowestStatements(topSlowest);
result.Data = data;
result.IsSuccess = data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult DeleteWorkload(DeleteWorkloadRequest request)
{
BaseOperationResponse result = new BaseOperationResponse()
{
IsSuccess = false
};
if (request != null)
{
HandleException(() =>
{
var repository = DALRepositories.GetWorkloadsRepository();
var workload = repository.GetByPrimaryKey(request.WorkloadID);
if (workload != null)
{
if (workload.InactiveDate == null)
{
workload.InactiveDate = DateTime.Now;
repository.Update(workload);
}
result.IsSuccess = true;
}
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetStoredProcedureStatistics(GetStoredProcedureStatisticsRequest request)
{
GetStoredProcedureStatisticsResponse result = new GetStoredProcedureStatisticsResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new StoredProcedureStatisticsData();
var totalStoredProcStatsRepository = DALRepositories.GetTotalStoredProcedureStatisticsRepository();
data.TotalStoredProcedureStatistics = totalStoredProcStatsRepository.GetAllForProcedure(request.StoredProcedureID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
result.Data = data;
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult UpdateConfiguration(ConfigurationData configuration)
{
BaseOperationResponse result = new BaseOperationResponse();
HandleException(() =>
{
var repository = DALRepositories.GetSettingPropertiesRepository();
var originalSmtp = repository.GetObject <DAL.Contracts.SmtpConfiguration>(DAL.Contracts.SettingPropertyKeys.SMTP_CONFIGURATION);
var smtp = Converter.CreateSmtpConfiguration(configuration.Smtp, originalSmtp);
repository.SetObject(DAL.Contracts.SettingPropertyKeys.SMTP_CONFIGURATION, smtp);
var reporting = Converter.CreateReportingSettings(configuration.Reports);
repository.SetObject(DAL.Contracts.SettingPropertyKeys.REPORTING_SETTINGS, reporting);
var collector = Converter.CreateCollectorConfiguration(configuration.Collector);
repository.SetObject(DAL.Contracts.SettingPropertyKeys.COLLECTOR_CONFIGURATION, collector);
result.IsSuccess = true;
}, ex => result.ErrorMessage = ex.Message);
return(Json(result));
}
public IActionResult GetConfiguration()
{
BaseResponse <ConfigurationData> result = new BaseResponse <ConfigurationData>();
HandleException(() =>
{
var repository = DALRepositories.GetSettingPropertiesRepository();
var data = new ConfigurationData();
var smtp = repository.GetObject <DAL.Contracts.SmtpConfiguration>(DAL.Contracts.SettingPropertyKeys.SMTP_CONFIGURATION);
data.Smtp = Converter.Convert(smtp);
var reporting = repository.GetObject <DAL.Contracts.ReportingSettings>(DAL.Contracts.SettingPropertyKeys.REPORTING_SETTINGS);
data.Reports = Converter.Convert(reporting);
var collectorConfiguration = repository.GetObject <DAL.Contracts.CollectorConfiguration>(DAL.Contracts.SettingPropertyKeys.COLLECTOR_CONFIGURATION);
data.Collector = Converter.Convert(collectorConfiguration);
result.Data = data;
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
return(Json(result));
}
public IActionResult GetIndexStatistics(GetIndexStatisticsRequest request)
{
GetIndexStatisticsResponse result = new GetIndexStatisticsResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new IndexStatisticsData();
var totalIndexStatsRepository = DALRepositories.GetTotalIndexStatisticsRepository();
var statementIndexStatisticsRepository = DALRepositories.GetNormalizedStatementIndexStatisticsRepository();
data.TotalIndexStatistics = totalIndexStatsRepository.GetAllForIndex(request.IndexID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
data.IndexSummaryStatementStatistics = statementIndexStatisticsRepository.GetIndexSummaryStatementStatistics(request.IndexID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
result.Data = data;
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetStatementsStatistics(GetStatementsStatisticsRequest request)
{
GetStatementsStatisticsResponse result = new GetStatementsStatisticsResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new StatementsStatisticsData();
var normalizedStatementStatisticsRepository = DALRepositories.GetNormalizedStatementStatisticsRepository();
data.SummaryNormalizedStatementStatistics = normalizedStatementStatisticsRepository.GetSummaryTotalStatementStatistics(request.DatabaseID,
request.Filter.DateFrom, request.Filter.DateTo, DAL.Contracts.SummaryNormalizedStatementStatisticsOrderBy.MaxDuration,
Converter.CreateCommandTypeFilter(request.Filter.CommandType), null).ToList();
result.Data = data;
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetWorkloads(GetWorkloadsRequest request)
{
GetWorkloadsReponse result = new GetWorkloadsReponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var repository = DALRepositories.GetWorkloadsRepository();
var workloads = repository.GetForDatabase(request.DatabaseID, request.Filter.DateFrom, request.Filter.DateTo, onlyActive: true);
result.Data = new List <WorkloadData>();
foreach (var w in workloads)
{
result.Data.Add(Converter.Convert(w));
}
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetStatementStatistics(GetStatementStatisticsRequest request)
{
GetStatementStatisticsResponse result = new GetStatementStatisticsResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new StatementStatisticsData();
var totalStatsRepository = DALRepositories.GetNormalizedStatementStatisticsRepository();
var relationStatsRepository = DALRepositories.GetNormalizedStatementRelationStatisticsRepository();
var indexStatsRepository = DALRepositories.GetNormalizedStatementIndexStatisticsRepository();
data.StatementTimeline = totalStatsRepository.GetTimelineForStatement(request.StatementID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
data.StatementRelationStatistics = relationStatsRepository.GetAllForStatement(request.StatementID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
data.StatementIndexStatistics = indexStatsRepository.GetAllForStatement(request.StatementID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
data.SlowestRepresentatives = totalStatsRepository.GetSlowestForStatement(request.StatementID, request.Filter.DateFrom, request.Filter.DateTo, 10).ToList();
result.Data = data;
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetWorkloadAnalysisDetail(GetWorkloadAnalysisDetailRequest request)
{
GetWorkloadAnalysisDetailReponse result = new GetWorkloadAnalysisDetailReponse();
if (request != null)
{
HandleException(() =>
{
var environmentsRepository = DALRepositories.GetVirtualEnvironmentsRepository();
var indicesRepository = DALRepositories.GetPossibleIndicesRepository();
var indicesEnvs = environmentsRepository.GetAllForWorkloadAnalysis(request.WorkloadAnalysisID, DAL.Contracts.VirtualEnvironmentType.Indices);
var hPartsEnvs = environmentsRepository.GetAllForWorkloadAnalysis(request.WorkloadAnalysisID, DAL.Contracts.VirtualEnvironmentType.HPartitionings);
var indicesIds = indicesEnvs.SelectMany(x => x.VirtualEnvironmentPossibleIndices).Select(x => x.PossibleIndexID).ToHashSet();
var indices = indicesRepository.GetByIds(indicesIds);
result.Data = Converter.ConvertToWorkloadAnalysisDetail(indicesEnvs, hPartsEnvs, indices);
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult CreateWorkloadAnalysis(CreateWorkloadAnalysisRequest request)
{
BaseOperationResponse result = new BaseOperationResponse()
{
IsSuccess = false
};
(bool isValid, string errorMessage) = WorkloadAnalysisValidator.Validate(request);
if (!isValid)
{
result.ErrorMessage = errorMessage;
}
else
{
HandleException(() =>
{
var workloadAnalysis = Converter.CreateWorkloadAnalysis(request);
var repository = DALRepositories.GetWorkloadAnalysesRepository();
repository.Create(workloadAnalysis);
result.IsSuccess = true;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
public IActionResult GetUnusedObjects(GetUnusedObjectsRequest request)
{
GetUnusedObjectsResponse result = new GetUnusedObjectsResponse();
if (request != null)
{
HandleException(() =>
{
result.Data = new UnusedObjectsData();
result.Data.Objects = new List <UnusedObjectData>();
var now = DateTime.Now;
DateTime thresholdDate = now.AddDays(-30);
var totalIndexStatsRepo = DALRepositories.GetTotalIndexStatisticsRepository();
var totalRelationStatsRepo = DALRepositories.GetTotalRelationStatisticsRepository();
var totalProceduresStatsRepo = DALRepositories.GetTotalStoredProcedureStatisticsRepository();
var totalViewsStatsRepo = DALRepositories.GetTotalViewStatisticsRepository();
bool areDataAvailableForWholePeriod = totalIndexStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalRelationStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalProceduresStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalViewsStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date);
var indicesLastKnownCollectionDate = totalIndexStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var relationsLastKnownCollectionDate = totalRelationStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var proceduresLastKnownCollectionDate = totalProceduresStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var viewsLastKnownCollectionDate = totalViewsStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
using (var scope = Converter.CreateDatabaseScope(request.DatabaseID))
{
foreach (var i in DBMSRepositories.GetIndicesRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (indicesLastKnownCollectionDate.TryGetValue(i.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused, LastKnownUsageDate = lastKnownUsageDate,
Name = i.Name + $" ON {i.SchemaName}.{i.RelationName}", ObjectType = "Index", SchemaName = i.SchemaName
});
}
foreach (var r in DBMSRepositories.GetRelationsRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (relationsLastKnownCollectionDate.TryGetValue(r.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = r.Name,
ObjectType = "Relation",
SchemaName = r.SchemaName
});
}
foreach (var p in DBMSRepositories.GetStoredProceduresRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (proceduresLastKnownCollectionDate.TryGetValue(p.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = p.Name,
ObjectType = "Stored procedure",
SchemaName = p.SchemaName
});
}
foreach (var v in DBMSRepositories.GetViewsRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (viewsLastKnownCollectionDate.TryGetValue(v.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = v.Name,
ObjectType = "View",
SchemaName = v.SchemaName
});
}
}
result.Data.Objects.Sort((x, y) =>
{
var cmp = (x.LastKnownUsageDate?.Ticks ?? double.MaxValue).CompareTo(y.LastKnownUsageDate?.Ticks ?? double.MaxValue);
if (cmp == 0)
{
cmp = x.SchemaName.CompareTo(y.SchemaName);
if (cmp == 0)
{
cmp = x.Name.CompareTo(y.Name);
}
}
return(cmp);
});
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
