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); }