We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been using them in its plans (because they weren't trusted). I have two questions before setting them back to trusted.
Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans?
Is it safe to say this is a situation where SQL Server has been rebuilding the index (we do have this turned on), and maintaining the index throughout inserts, updates, and deletes, but simply not using the foreign key?