This is like fixing bad parameter sniffing. In order for the execution plan to change, there had to be a recompile. Causing a recompile is frequently the fix for bad parameter sniffing or a stale execution plan.
I'm also thinking that the index might not actually be needed but still caused the recompile to occur. To find out for sure, simply disable the new index and see what happens on a couple of runs. If things go back to being slow, rebuild the index to bring it back into play.
Like Grant says, an index doesn't every have to actually be used to be valuable. I can, in fact, produce statistics that the optimizer looks at that can cause it to make different, sometimes better, choices.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)