July 2, 2013 at 12:43 am
Hi,
When I am creating the index on 1 table of the sp, it is executing faster. after creating the Index, when I just alter the same sp with no any other changes in the code, that sp is taking time and execution plan is being chnaged.
Aaing if i am Drop & creating the same index, sp executing fatser but after again alter it is performing slow.
What can be reason of change the execution plan only after alter sp even no any code change is there.
Please suggest.
July 2, 2013 at 2:22 am
Sounds to me like you have a an operation in your sp which can affect the way the optimizer calculates the plan. This could be something like a parameter in a join clause which dramatically changes the row counts etc... Sounds like the best thing to do would be to create the procedure using the WITH RECOMPILE option. I'd imagine that the overhead of recompilation will be low compared with using the wrong plan.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply