We've ran into an issue in production a couple of times now where it looks like a stored procedure gets a new, bad plan and starts causing havoc. Unfortunately, we weren't able to capture the plan before and after to see what the bad plan looked like before we did a dbcc freeproccache, we also don't have query store set up.
My question is, what all can cause sql server to generate a new plan for a stored procedure? Especially for an sp that is ran all of the time. Some of the ones I know are:
- dbcc freeproccache
- rebooting the server
- option recompile