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.

Answer 3

From the page "Query Performance Tuning"

Improve Indexes

This page has many helpful step-by-step hints on how to tune your indexes for best performance, and what to watch for (profiling).

As with most performance optimization techniques, there are tradeoffs. For example, with more indexes, SELECT queries will potentially run faster. However, DML (INSERT, UPDATE, and DELETE) operations will slow down significantly because more indexes must be maintained with each operation. Therefore, if your queries are mostly SELECT statements, more indexes can be helpful. If your application performs many DML operations, you should be conservative with the number of indexes you create.

Other resources:

• http://databases.about.com/od/sqlserver/a/indextuning.htm

However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum

• http://searchsqlserver.techtarget.com/tip/Stored-procedure-to-find-fragmented-indexes-in-SQL-Server

Fragmented indexes and tables in SQL Server can slow down application performance. Here's a stored procedure that finds fragmented indexes in SQL servers and databases.

Answer 4

Ok . First off, index's slow down two things (at least)

-> insert/update/delete : index rebuild

-> query planning : "shall I use that index or not ?"

Someone mentioned the query planner might take a less efficient route - this is not supposed to happen.

If your optimizer is even half-decent, and your statistics / parameters correct, there is no way it's going to pick the wrong plan.

Either way, in your case (mssql), you can hardly trust the optimizer and will still have to check every time.

What you're currently doing looks quite sound, you should just make sure the data you're looking at is relevant, i.e. real use case queries in the right proportion (this can make a world of difference).

In order to do that I always advise to write a benchmarking script based on real use - through logging of production-env. queries, a bit like I said here :

Complete db schema transformation - how to test rewritten queries?