public void TryAddPossibleCoveringIndices(IEnumerable <IndexDefinition> indexDefinitions, NormalizedStatement normalizedStatement, StatementQuery query)
{
TryAddPossibleIndices(indexDefinitions, normalizedStatement, query);
var queryKey = new NormalizedStatementQueryPair(normalizedStatement.ID, query);
if (!allCoveringPerQuery.ContainsKey(queryKey))
{
allCoveringPerQuery.Add(queryKey, new HashSet <IndexDefinition>());
}
foreach (var indexDefinition in indexDefinitions)
{
allCoveringPerQuery[queryKey].Add(indexDefinition);
}
}
public WorkloadStatementsData(IEnumerable <NormalizedWorkloadStatement> data)
{
long allExecutionsCount = 0;
Dictionary <uint, decimal> allSelectQueriesTotalExecutionsByRelation = new Dictionary <uint, decimal>();
foreach (var workloadStatement in data)
{
allExecutionsCount += workloadStatement.TotalExecutionsCount;
var statementCommandType = workloadStatement.NormalizedStatement.StatementDefinition.CommandType;
all.Add(workloadStatement.NormalizedStatement.ID, workloadStatement);
if (statementCommandType == StatementQueryCommandType.Select)
{
allSelects.Add(workloadStatement.NormalizedStatement.ID, workloadStatement);
}
else if (statementCommandType == StatementQueryCommandType.Update)
{
allUpdates.Add(workloadStatement.NormalizedStatement.ID, workloadStatement);
}
foreach (var query in workloadStatement.NormalizedStatement.StatementDefinition.IndependentQueries)
{
var queryStatement = new NormalizedStatementQueryPair(workloadStatement.NormalizedStatement.ID, query);
foreach (var r in query.Relations)
{
if (query.CommandType == StatementQueryCommandType.Select)
{
if (!allSelectQueriesByRelation.ContainsKey(r.ID))
{
allSelectQueriesByRelation.Add(r.ID, new List <NormalizedStatementQueryPair>());
}
allSelectQueriesByRelation[r.ID].Add(queryStatement);
if (!allSelectQueriesTotalExecutionsByRelation.ContainsKey(r.ID))
{
allSelectQueriesTotalExecutionsByRelation.Add(r.ID, 0);
}
allSelectQueriesTotalExecutionsByRelation[r.ID] += workloadStatement.TotalExecutionsCount;
}
if (!allQueriesByRelation.ContainsKey(r.ID))
{
allQueriesByRelation.Add(r.ID, new List <NormalizedStatementQueryPair>());
}
allQueriesByRelation[r.ID].Add(queryStatement);
}
}
}
PrepareMostSignificantData(allSelectQueriesTotalExecutionsByRelation);
AllExecutionsCount = allExecutionsCount;
}
protected override void OnExecute()
{
context.IndicesDesignData.PossibleIndexSizes.Clear();
#if DEBUG
int counter = 0;
#endif
using (var scope = new DatabaseScope(context.Database.Name))
{
foreach (var env in context.IndicesDesignData.Environments)
{
#if DEBUG
if (System.Diagnostics.Debugger.IsAttached)
{
++counter;
if (counter % 10 == 0)
{
Console.WriteLine(((counter / (double)context.IndicesDesignData.Environments.Count) * 100).ToString("F2") + "%");
}
}
#endif
try
{
virtualIndicesRepository.DestroyAll();
var virtualIndicesMapping = new Dictionary <IndexDefinition, IVirtualIndex>();
bool indicesCreated = false;
try
{
foreach (var index in env.PossibleIndices.All)
{
var targetRelationData = context.RelationsData.GetReplacementOrOriginal(index.Relation.ID);
var virtualIndex = virtualIndicesRepository.Create(dbObjectDefinitionGenerator.Generate(index.WithReplacedRelation(targetRelationData)));
if (virtualIndex != null)
{
virtualIndicesMapping.Add(index, virtualIndex);
}
if (!context.IndicesDesignData.PossibleIndexSizes.ContainsKey(index))
{
context.IndicesDesignData.PossibleIndexSizes.Add(index, virtualIndicesRepository.GetVirtualIndexSize(virtualIndex.ID));
}
}
indicesCreated = true;
}
catch (Exception ex)
{
log.Write(ex);
}
if (indicesCreated)
{
foreach (var kv in env.PossibleIndices.AllPerStatement)
{
var statementID = kv.Key;
var indices = kv.Value;
var workloadStatement = context.StatementsData.All[statementID];
var normalizedStatement = workloadStatement.NormalizedStatement;
var representativeStatement = workloadStatement.RepresentativeStatistics.RepresentativeStatement;
var statementToUse = RepresentativeStatementReplacementUtility.Provide(normalizedStatement, representativeStatement, context.RelationsData);
var explainResult = explainRepository.Eplain(statementToUse);
var latestPlan = explainResult.Plan;
var realPlan = context.RealExecutionPlansForStatements[statementID].Plan;
HashSet <IndexDefinition> improvingVirtualIndices = new HashSet <IndexDefinition>();
VirtualEnvironmentStatementEvaluation statementEvaluation = new VirtualEnvironmentStatementEvaluation();
statementEvaluation.ExecutionPlan = explainResult;
decimal fromPrice = realPlan.TotalCost;
decimal toPrice = latestPlan.TotalCost;
decimal divisor = Math.Abs(Math.Max(fromPrice, toPrice));
if (divisor > 0)
{
statementEvaluation.LocalImprovementRatio = ((toPrice - fromPrice) / divisor) * -1m;
}
decimal statementPortion = context.StatementsData.AllExecutionsCount > 0 ? workloadStatement.TotalExecutionsCount / (decimal)context.StatementsData.AllExecutionsCount : 0m;
statementEvaluation.GlobalImprovementRatio = statementEvaluation.LocalImprovementRatio * statementPortion;
env.StatementsEvaluation.Add(statementID, statementEvaluation);
foreach (var i in indices)
{
var virtualIndex = virtualIndicesMapping[i];
if (!env.IndicesEvaluation.ContainsKey(i))
{
env.IndicesEvaluation.Add(i, new VirtualIndicesEnvironmentIndexEvaluation());
}
if (explainResult.UsedIndexScanIndices.Contains(virtualIndex.Name))
{
statementEvaluation.UsedIndices.Add(i);
statementEvaluation.AffectingIndices.Add(i);
if (latestPlan.TotalCost < realPlan.TotalCost)
{
improvingVirtualIndices.Add(i);
}
env.IndicesEvaluation[i].ImprovementRatio += statementEvaluation.GlobalImprovementRatio;
}
else
{
if (normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Insert ||
normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Update ||
normalizedStatement.CommandType == DAL.Contracts.StatementQueryCommandType.Delete)
{
statementEvaluation.AffectingIndices.Add(i);
}
}
}
foreach (var query in normalizedStatement.StatementDefinition.IndependentQueries)
{
var queryKey = new NormalizedStatementQueryPair(statementID, query);
env.ImprovingPossibleIndices.TryAddPossibleIndices(improvingVirtualIndices, normalizedStatement, query);
if (env.PossibleIndices.AllCoveringPerQuery.ContainsKey(queryKey))
{
env.ImprovingPossibleIndices.TryAddPossibleCoveringIndices(env.PossibleIndices.AllCoveringPerQuery[queryKey].Intersect(improvingVirtualIndices), normalizedStatement, query);
}
}
}
}
}
finally
{
virtualIndicesRepository.DestroyAll();
}
}
}
}