Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution Plan being changed. Expand / Collapse
Author
Message
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, June 9, 2014 1:01 AM
Points: 40, Visits: 169
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.
Post #1469310
Posted Tuesday, July 2, 2013 2:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:39 AM
Points: 157, Visits: 293
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.

http://msdn.microsoft.com/en-us/library/ms190439.aspx
Post #1469339
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse