What causes sql server to generate a new plan for a stored procedure? 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
For SQL 2016 here is the list why 'statement_recompile_cause' can happen.


Schema changed
Statistics changed
Deferred compile
Set option change 
Temp table changed 
Remote rowset changed
For browse permissions changed
Query notification environment changed
PartitionView changed
Cursor options changed
Option (recompile) requested
Parameterized plan flushed
Test plan linearization
Plan affecting database version changed
Query Store plan forcing policy changed
Query Store plan forcing failed
Query Store missing the plan


You can get the same list for any other versions that support Extended Event tracing.

 Select 
  [name],
  map_key,
  map_value
FROM sys.dm_xe_map_values 
WHERE [name] ='statement_recompile_cause' 


Few good references:


A good article to read by Bhavesh Patel
Another Q&amp;A in StackOverflow.
When does a Stored Procedure recompile?(Sql Server)