title: What do you do to make sure a new index does not slow down queries?
body: <p>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:</p>
  
  <ol>
  <li>start the Profiler,   </li>
  <li>run a SQL script which contains lots of queries I do not want to slow down  </li>
  <li>load the trace from a file into a table,   </li>
  <li>analyze CPU, reads, and writes from the trace against the results from the previous runs, before I added (or removed) an index.</li>
  </ol>
  
  <p>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?</p>
  
  <p><strong>Edit 1</strong> The person who voted to close my question, could you explain your reasons?</p>
  
  <p><strong>Edit 2</strong> 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.</p>
  
  <p><strong>Edit 3</strong> 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.</p>
  
  <p>Another example is adding an index on increasing column, such as OrderDate, can seriously slow down a query with a condition like OrderDate>SomeDateAfterCreatingTheIndex.</p>