Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question

migrated from stackoverflow.com Jan 26 '12 at 14:34

2 Answers

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
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
+1 for main points + article – AdaTheDev Feb 4 '10 at 12:33
up vote 4 down vote

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

 
discard

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.