Separate primary key from other indexes for ignore index option.
Our indexes are tuned and maintained in production. Database development is done elsewhere and moved using SQL Compare. Therefore we don't want to compare indexes. However, if we don't compare indexes, then the primary keys are not created. If we do compare indexes, there is a real risk that production will be damaged.
This problem could be prevented by treating primary key indexes differently from other indexes. That this is even necessary is an implementation quirk of SQL Server.
Peter Harris
shared this idea
In SQL Compare 12.2.3.4239, 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.
Feedback on this feature can be posted on the forum thread:
https://forum.red-gate.com/discussion/80770/new-feature-ignore-performance-indexes
-
WesH
commented
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.
-
Alessandro Alpi commented
Yes Ami, you're right, but I guess that with "primary key" Peter means "indexes that are created with the PK constraint". It would be good to have something like "Ignore Index Created By PK and UK constraint" in addition to other options (as Wes proposed)
-
Ami
commented
Primary keys and unique constraints are NOT indexes.
They happen to have indexes that support them, but they are 1st grade citizens of the database.
I do not expect those to be ignored when I check "Ignore indexes". -
Wes
commented
Agreed. We unknowingly dropped all of the primary keys in our databases early in our SQL Compare usage because we didn't want to bring all of the production indexes into development.
I would like it to be split into a few different options.
Constrained Indexes: (Primary Key and Unique Indexes) These indexes we always want synched because they significantly affect data quality.Clustered Indexes: We would usually want these brought down as they affect the physical table.
Non-clustered indexes: These are generally used to tune an environment for a specific workload and we would like to ignore them. A number of our differences are NCIs that we do not want to sync.
-
Every environment should/could have a set of dedicate indexes. Every production environment needs to be fine-tuned and it's likely that someone of them need different approaches when indexing. Additionally, when a database is installed more than once, each setup needs different indexes..
-
Michael
commented
This will really help also our production deployment.