how can I fix plan for stored procedure?

Hello,

Is it possible to fix plan for stored procedure?
Is it possible to fix NestedLoops Optimized?

Thanks

Regards

bibi
bibi92Asked:
Who is Participating?
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Add with recompile to have the stored proc generate a new plan.

Is there any reason you are looping?  If you post your query there might be ways of doing set based info.

Otherwise the loops are what they are.
0
 
LIONKINGCommented:
When you say "fix" plan, do you mean use the same plan each time, use a different plan each time or "improve" the execution plan?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
If by "fix" you mean "correct", which I think is what you mean, then ge325's suggestion of WITH RECOMPILE is what you need.

A "bad" nested loops is almost always the result of bad statistics.  You likely need to update the statistics on a table(s).


[
If by "fix" you mean "force it to be the same", you might be able to force a certain plan structure on a query with a "Plan Guide".  It's highly unusual, though, to need, or even want, that.
]
0
 
bibi92Author Commented:
Hello,

Thanks. How can I force a certain plan structure on a query with a "Plan Guide".  

Regards

bibi
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.