Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Jan 26 '12 at 14:34

This question came from our site for professional and enthusiast programmers.

up vote 5 down vote accepted

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

share|improve this answer
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
+1 for main points + article – AdaTheDev Feb 4 '10 at 12:33

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)

share|improve this answer
+1 nicely covers my "while being used" point... – gbn Feb 4 '10 at 12:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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