1

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use?

Similar question: is there a way to pro-actively restore the buffer cache during or after a database restore?,…. other than to create and run select statements on the tables previously in the buffer cache, a solution which would be time-consuming to create, and inevitably inaccurate.

This is a followup to this question: "Does restoring database clear buffer cache and stored proc cache". edit: This question is a follow-up because I want to know if there is a workaround to get the plan cache and buffer pool back to their source state.

4
  • Isn't this question answered by Mark in the linked question? You can't re-use existing plan cache for a restored database. Also according to the doc the restore command clears the plan cache for the entire instance.
    – kevinnwhat
    Oct 18, 2019 at 20:53
  • @kevinnwhat I agree that the docs appear to say that restoring clears the entire instance cache-- however, that is not the case. The cache will only be clear plans for the database that is being restored.
    – AMtwo
    Oct 19, 2019 at 12:06
  • 1
    @Phil, it's not a duplicate question. It's a follow-up, as stated in the OP.
    – Conrad S.
    Oct 20, 2019 at 4:55
  • 1
    @kevinnwhat, those links you listed are the reason for my question, i.e. is there any way around those blockers? I'll bet there's a third party tool out there that does it, because it's a legitimate need, the commented links notwithstanding.
    – Conrad S.
    Oct 20, 2019 at 5:07

1 Answer 1

3

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use?

No. The caches will have to warm up based on use. You can force plans using the Query Store for particular queries if you want, as the Query Store is part of the database, and will be present after Restore.

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