We've been getting a lot of bad plans for our Stored Procedures. It's a vicious cycle:
- A Stored Procedure runs fine for a week (let's say executes under 0.1 seconds)
- Out of nowhere it gets a bad plan and it's taking 10+ seconds, which is really bad because it's getting called maybe 50 times a minute
- We recompile it (i.e. sp_recompile) and then it's good
Any advice from those who have dealt with this issue. I'm wondering about forcing SQL Server to keep plans (i.e. not the bad ones). We're looking at query hints too.