[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can i fix execution plan of stored procedure or user defined function in SQL Server 2005

Posted on 2008-11-04
4
Medium Priority
?
186 Views
Last Modified: 2011-10-19
I have SQL Server 2005. I want to know how can i fix and change execution plan of stored procedure or user defined function?

Also, want to know, if already I fix excution plan, when automatically it gets changed, and what to do to avoid it.

I have stored procedure, which run several times in an hour (lets say 500-800 times in an hour), i have observered that execution plan gets changed frequently. So, if i can fix execution plan, than i can take maximum advantage of indexes i created.
0
Comment
Question by:ferik
  • 2
  • 2
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22882966
to change the execution plan, you'll need to either change the query OR include query and/or join hints.  You can also use plan guides

post your procedure code.
0
 

Author Comment

by:ferik
ID: 22895062
I have attached procedure code. basically this procedure works like notification to different application to find out further steps require to take or not.

Please advice.

Proc.txt
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 750 total points
ID: 22895110
Are there indexes on the LCode, BID,SID and stID fields?
0
 

Author Comment

by:ferik
ID: 22910970
Yes, there are indexes on all those fields. Please note that all indexes are seperate. No index has more than 1 column.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

831 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