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 GetRelationStatistics(GetRelationStatisticsRequest request)
{
GetRelationStatisticsResponse result = new GetRelationStatisticsResponse();
if (request != null && request.Filter != null)
{
HandleException(() =>
{
var data = new RelationStatisticsData();
var totalRelationStatsRepository = DALRepositories.GetTotalRelationStatisticsRepository();
var statementRelationStatisticsRepository = DALRepositories.GetNormalizedStatementRelationStatisticsRepository();
data.TotalRelationStatistics = totalRelationStatsRepository.GetAllForRelation(request.RelationID, request.Filter.DateFrom, request.Filter.DateTo).ToList();
data.RelationSummaryStatementStatistics = statementRelationStatisticsRepository.GetRelationSummaryStatementStatistics(request.RelationID, 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 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));
}