0

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
6

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)
| improve this answer | |
  • Alter proc ,dynamic sql without sp_execute – KumarHarsh Jan 6 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.