New feature: Ignore performance indexes

Andrew F

In SQL Compare 12.2.3, released today, we have made a start on giving users more fine grained control over exactly which indexes are deployed.

We have added a new `Ignore performance indexes' option which will ignore all indexes except primary keys and unique constraints.

We realise that there is more we can do in this area, so we would like to hear your feedback about whether this new option is helpful and how we can do more.

wdhenrik

I'm glad this feature has been added, but I think some additional tweaks are needed.
Currently, IGNORE INDEXES (II) and IGNORE PERFORMANCE INDEXES (IPI) are tightly coupled. If I select II, then IPI is selected as well. More importantly, if I unselect IPI, II is deselected as well.

In the current implementation, changing my uniqueness constraint will also require comparing all of my performance tuning indexes. My production environment will always have different performance indexes. Changing the data quality constraint does not mean all of my other performance tuning indexes are invalidated.

I think these features need to be decoupled, with Ignore Indexes being removed and replaced with a more targeted option.
1. Ignore primary key and unique constraints: Ignores unique constraints & primary keys, including the associated indexes that are used to enforce data quality. (Replaces ignore indexes)
2. Ignore performance indexes: Ignores clustered & non-clustered indexes that do not affect data quality. Does not include primary key or unique constraint indexes.

I understand this may require invalidating Saved Defaults, but I think the clarity is worth it. Although the new performance indexes feature has value, I cannot use it to update unique/primary key constraints without risking the performance of the target environment.

Andrew F

Thanks for your reply.

@wdhenrik said:
In the current implementation, changing my uniqueness constraint will also require comparing all of my performance tuning indexes. My production environment will always have different performance indexes. Changing the data quality constraint does not mean all of my other performance tuning indexes are invalidated.

Does setting 'ignore performance indexes' (without 'Ignore indexes') give you the desired behaviour here? As I understand it you want to consider only your uniqueness constraints without any performance tuning indexes, which is what the new option provides.

I agree with your general point that, with the addition of 'ignore performance indexes', there is scope for rethinking how we structure the various index options.

wdhenrik

Andrew F wrote: »

Does setting 'ignore performance indexes' (without 'Ignore indexes') give you the desired behavior here?

Yes, unselecting II/selecting IPI does allow me to compare constrained indexes without affecting my performance indexes.

However these two option need to be decoupled.
1. II is badly named and does not distinguish itself from IPI, or mention IPI.
2. The description of IPI feels like a combination of double negatives that make it confusing. IPI claims to ignore everything from II, except primary key and unique constraints. However, II also ignores statistics, which is an additional explicit option that is not part of the II/IPI coupling. Does selecting IPI also ignore statistics? If yes, why doesn't it select the IS option as well?

I still think these options should be decoupled, the II name changed and the descriptive text updated to better explain them.

Mark J

Thanks for the details, that's really helpful. It's good to know that we're not actually blocking what you want to do, but we can definitely look into improving the option descriptions to be clearer about how they relate to each other.

I'll also have a look into pulling the options apart some more - this might be trickier since it is important to us that we preserve behavior for people using existing project files, but I'll see what we can do.