What causes sql server to generate a new plan for a stored procedure?

Question

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

Answer 1

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:

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