private string GetIdenticalOrOverlapAnalysisSummary(IndexPairComparisonInfo ci)
{
// We're not analyzing indexes with different filters for now, we'll consider them unrelated, because usually if you are using filters you are addressing
// a performance concern.
if (!ci.HasSameFilter)
{
return(null);
}
// indexes that are identical (pairs where one of them can be safely dropped
if (ci.HasSameFilter && ci.HasSameIndexedColumnNames && ci.HasSameIndexedColumnOrder && ci.HasSameIndexedColumnDescending && ci.HasSameIncludedColumns)
{
if (ci.HasClusteredPkIndex)
{
return($"Remove {GetOtherById(ci.PkIndex).IndexName}");
}
else if (!ci.HasPkIndex && ci.HasClusteredUniqueIndex)
{
return($"Remove {GetOtherById(ci.ClusteredIndex).IndexName}");
}
else if (!ci.HasPkIndex && ci.HasClusteredIndex && !ci.HasUniqueIndex)
{
return($"Remove {GetOtherById(ci.ClusteredIndex).IndexName}");
}
else if (!ci.HasPkIndex && !ci.HasClusteredIndex && ci.HasUniqueIndex)
{
return($"Remove {GetOtherById(ci.UniqueIndex).IndexName}");
}
else if (!ci.HasPkIndex && !ci.HasClusteredIndex && !ci.HasUniqueIndex)
{
return("Remove either index");
}
}
if (ci.HasSameFilter && ci.HasSameIndexedColumnNames && ci.HasSameIndexedColumnOrder && ci.HasSameIndexedColumnDescending && ci.HasProperSubsetOfIncludedColumns)
{
if (!ci.HasPkIndex || (ci.PkIndex != ci.IndexWithProperSubsetOfIncludedColumns))
{
return($"Remove {GetById(ci.IndexWithProperSubsetOfIncludedColumns).IndexName}");
}
}
if (ci.HasSameFilter && ci.HasOrderedProperSubsetOfIndexedColumns && ci.HasSameIndexedColumnDescending && ci.HasSameIncludedColumns && !ci.HasPkIndex && !ci.HasClusteredIndex && !ci.HasUniqueIndex)
{
return($"Remove {GetById(ci.IndexWithOrderedProperSubsetOfIndexedColumns).IndexName}");
}
if (ci.HasSameFilter && ci.HasOrderedProperSubsetOfIndexedColumns && ci.HasSameIndexedColumnDescending && !ci.HasPkIndex && !ci.HasClusteredIndex && ci.HasUniqueIndex && ci.UniqueIndexHasMoreColumns)
{
return($"Remove {GetOtherById(ci.UniqueIndex).IndexName}");
}
if (ci.HasSameFilter && ci.HasOrderedProperSubsetOfIndexedColumns && ci.HasSameIndexedColumnDescending && !ci.HasPkIndex && ci.HasClusteredIndex && !ci.HasUniqueIndex && ci.ClusteredIndexHasMoreColumns)
{
return($"Remove {GetOtherById(ci.ClusteredIndex).IndexName}");
}
return(null);
}
// This method needs work, only covers one unusual case for now
// TODO: handle case where indexed columns are a subset, but include columns are different
private string GetCanPossiblyBeMergedAnalysisSummary(IndexPairComparisonInfo ci)
{
// indexes that might be consolidated (pairs where, with some changes to one, the other may be dropped, but analysis is needed to be certain)
if (ci.HasSameFilter && ci.HasOrderedProperSubsetOfIndexedColumns && ci.HasSameIndexedColumnDescending && ci.HasPkIndex && !ci.HasClusteredPkIndex && ci.HasClusteredIndex && !ci.HasUniqueIndex && ci.PkIndexHasMoreColumns)
{
return($"Consider making {GetById(ci.PkIndex).IndexName} clustered and removing {GetOtherById(ci.PkIndex).IndexName}");
}
return(null);
}
private string GetCanBeMergedAnalysisSummary(IndexPairComparisonInfo ci)
{
// indexes that can be consolidated (pairs where, with minor changes to one, the other may be dropped)
if (ci.HasSameFilter && ci.HasSameIndexedColumnNames && ci.HasSameIndexedColumnOrder && ci.HasSameIndexedColumnDescending && ci.HasSameIncludedColumns)
{
if (ci.HasPkIndex && !ci.HasClusteredPkIndex && ci.HasClusteredIndex)
{
return($"Make {GetById(ci.PkIndex).IndexName} clustered and remove {GetOtherById(ci.PkIndex).IndexName}");
}
else if (!ci.HasPkIndex && ci.HasClusteredIndex && ci.HasUniqueIndex)
{
return($"Make {GetById(ci.ClusteredIndex).IndexName} unique and remove {GetOtherById(ci.ClusteredIndex).IndexName}");
}
}
if (ci.HasSameFilter && ci.HasSameIndexedColumnNames && ci.HasSameIndexedColumnOrder && ci.HasSameIndexedColumnDescending && !ci.HasSameIncludedColumns && !ci.HasProperSubsetOfIncludedColumns)
{
return($"Merge the included columns into one index and remove the other");
}
return(null);
}
