Solved

how can I fix plan for stored procedure?

Posted on 2013-01-11
6
268 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

630 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