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

Posted on 2008-11-04
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.
Question by:ferik
    LVL 60

    Expert Comment

    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.

    Author Comment

    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.

    LVL 60

    Accepted Solution

    Are there indexes on the LCode, BID,SID and stID fields?

    Author Comment

    Yes, there are indexes on all those fields. Please note that all indexes are seperate. No index has more than 1 column.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In this article I will describe the Copy Database Wizard 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.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    779 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

    16 Experts available now in Live!

    Get 1:1 Help Now