4

Is there a measure of time frame as to which the execution plan of inline queries will cached?

6

There is an algorithm (will find it), but practically it will stay there:

  • while being used (Edit: See AdaTheDev's answer)
  • until statistics update (also implied by index rebuild)
  • until definition changes (including whitespace and case)
  • SQL instance restart
  • until severe memory pressure

SQL Server 2008 article

  • 1
    The stats update will invalidate the plan, but doesn't immediately remove it bizarrely. – Andrew Feb 4 '10 at 12:19
  • yes, seen that. also ALTER PROC (get "definition has been changed or similar' error). – gbn Feb 4 '10 at 12:25
4

As gbn said, there is an algorithm. Just to add that the length of time a plan will be cached for also relates to how valuable a plan is deemed to be and how often it is used (if it's a higher value plan, and it keeps getting used then it's value is very high so will be more likely to be held for longer than other plans)

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.