Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Execution Plan being changed. Expand / Collapse
Posted Tuesday, July 2, 2013 12:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 21, 2016 1:31 AM
Points: 40, Visits: 262
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.
Post #1469310
Posted Tuesday, July 2, 2013 2:22 AM


Group: General Forum Members
Last Login: Wednesday, June 29, 2016 9:39 AM
Points: 171, Visits: 361
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.
Post #1469339
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse