What do you do to make sure a new index does not slow down queries?

Question

When we add or remove a new index to speed up something, we may end up slowing down something else. To protect against such cases, after creating a new index I am doing the following steps:

1. start the Profiler,
2. run a SQL script which contains lots of queries I do not want to slow down
3. load the trace from a file into a table,
4. analyze CPU, reads, and writes from the trace against the results from the previous runs, before I added (or removed) an index.

This is kind of automated and kind of does what I want. However, I am not sure if there is a better way to do it. Is there some tool that does what I want?

Edit 1 The person who voted to close my question, could you explain your reasons?

Edit 2 I googled up but did not find anything that explains how adding an index can slow down selects. However, this is a well known fact, so there should be something somewhere. If nothing comes up, I can write up a few examples later on.

Edit 3 One such example is this: two columns are highly correlated, like height and weight. We have an index on height, which is not selective enough for our query. We add an index on weight, and run a query with two conditions: a range on height and a range on weight. because the optimizer is not aware of the correlation, it grossly underestimates the cardinality of our query.

Another example is adding an index on increasing column, such as OrderDate, can seriously slow down a query with a condition like OrderDate>SomeDateAfterCreatingTheIndex.

Answer 1

Ultimately what you're asking can be rephrased as 'How can I ensure that the queries that already use an optimal, fast, plan do not get 'optimized' into a worse execution plan?'.

Whether the plan changes due to parameter sniffing, statistics update or metadata changes (like adding a new index) the best answer I know of to keep the plan stable is plan guides. Deploying plan guides for critical queries that already have good execution plans is probably the best way to force the optimizer into keep using the good, validated, plan. See Applying a Fixed Query Plan to a Plan Guide:

You can apply a fixed query plan to a plan guide of type OBJECT or SQL. Plan guides that apply a fixed query plan are useful when you know about an existing execution plan that performs better than the one selected by the optimizer for a particular query.

The usual warnings apply as to any possible abuse of a feature that prevents the optimizer from using a plan which may be actually better than the plan guide.

Answer 2

How about the following approach:

• Save the execution plans of all typical queries.
• After applying new indexes, check which execution plans have changed.
• Test the performance of the queries with modified plans.