Take the 2-minute tour ×
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.

I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different even though they run against the same database.

Can anybody please explain why this occurred?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Most likely, either:

  1. The state of the database (e.g. statistics) changed since the cached plan was first compiled, causing a recompilation; or
  2. Your SSMS connection uses different settings (e.g. ANSI_NULLS) from those used by the connection that cached the plan (so it could not be reused); or
  3. It was an ad-hoc batch and the text of your query did not exactly match the cached version
  4. You used literal values where the original plan was parameterized
share|improve this answer

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.