Solved

how can I fix plan for stored procedure?

Posted on 2013-01-11
6
262 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
6 Comments
 
LVL 39

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:ScottPletcher
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 39

Accepted Solution

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

Author Closing Comment

by:bibi92
ID: 38892248
Thanks bibi
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now