Bad-plan epidemic

Transact-SQL
1

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
  • Repeat

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.

2

If plans are that fragile, you really need to step back and a take a broader look at indexes.

However, in the mean time, make sure the index statistics are up to date on tables, esp. the large/critical ones.