This drives me mad - previously some stored procs run in 100 ms or less - then suddenly jump to 2000-5000 ms without a by or leave.

We have tried using index hints in the past but data changes over time and the index hints start to slow the system down too.  There's got to be a way of forcing the server to use the good plan permanently.  We run the same stored proc millions of times a day (real time data collection) to insert data.  I am sure it is when the size of the tables reaches a threshold that SQl decides to change the plan. 

If you can identify which tables are the main culprits - I would run an update stats task (autoupdate doesn't hack it for me) - I also run an index rebuild on some of the smaller tables which are mainly varchars to bring them back in line.  This improves things but SQl still manages to throw a curve ball every month or so.

Sim

Manchester UK