Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how can I fix plan for stored procedure?

Posted on 2013-01-11
6
Medium Priority
?
269 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 70

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 2000 total points
ID: 38837210
0
 

Author Closing Comment

by:bibi92
ID: 38892248
Thanks bibi
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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