Solved

how can I fix plan for stored procedure?

Posted on 2013-01-11
6
266 Views
Last Modified: 2013-02-15
Hello,

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

Thanks

Regards

bibi
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38767357
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 38767739
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38767886
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:bibi92
ID: 38777884
Hello,

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

Regards

bibi
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 38837210
0
 

Author Closing Comment

by:bibi92
ID: 38892248
Thanks bibi
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question