• I believe dropping an index will cause any execution plans that use that index to be recompiled on the next run as the index is no longer valid. As you are then recreating the index, the same effect will be experienced again i.e. when the query is next executed the optimizer will have to build up a new plan with a new set of parameter values saved in the plan, these are the values that the optimizer assumes are standard and good to use.

    It sounds that the values saved are only really a good reflection of the data in the underlying tables for a few days. It may be worth reviewing the maintenance on the stats and seeing if having more frequent updates on the stats on the relevant tables may have a positive benefit:

    http://msdn.microsoft.com/en-us/library/ms187348.aspx

    http://msdn.microsoft.com/en-us/library/ms173804.aspx

    You could also look at using OPTION (recompile) for specific parts of the query where the issue is or even WITH RECOMPILE for the entire procedure. These will cause the optimizer to recompile either part or all of the plan respectively. I DON'T RECOMMEND THIS in general but it if you try it and things work better for days then it would tell you that you have negatively performing parameter sniffing and should take a look at ways to avoid that.

    Good luck in your investigations and please post how you get on.