Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Can I Manually Load SQL 2005 Execution Plans into Cache at Server Startup?

Posted on 2010-09-02
Last Modified: 2012-05-10
Every time I restart our main production SQL Server, performance sucks for a couple of days until the statistics and the execution plans are gradually compiled and loaded into cache.  Since these are stored in volatile memory, they go away whenever the SQL Server Service is shut down.

What I want to do:
I want to be able to take the saved output from ShowPlan XML and load into the Server cache at startup, so I don't have to wait for all of the plans to be recompiled.  I know that this can be done in Oracle.  Can it be done in SQL Server 2005, and if so, how?
Question by:SQLShark
  • 3
  • 3

Assisted Solution

almander earned 250 total points
ID: 33591762
This is a pretty in depth.


Author Comment

ID: 33591827
That is a good article.  I actually already have it, and the two sequels.  I can get the database statistics.

What I want are the execution plans.  As I said, these are stored in volatile memory, and they are dropped when the SQL Server service stops.

I can use SQL Profiler to collect the Execution Plans in XML format and save them to a file or a database table.

However, how do I then load these plans back into the (now empty) plan cache?

Expert Comment

ID: 33591884
Sorry, I misread your question, and jumped to an early conclusion.

Scratching head...
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


Author Comment

ID: 33591949
I understand.  It is a real head scratcher.  It seems like something you ought to be able to do.  I found an article detailing how to do exactly what I want to do - in Oracle 10g.  Unfortunately, that doesn't help.
LVL 24

Accepted Solution

DBAduck - Ben Miller earned 250 total points
ID: 33593420
You cannot import any plans into plan cache that I know of.  I have never been asked to do this.  Plan cache is built by SQL Server.  I am willing to stand corrected, but there certainly is no documentation that I know of that illustrates this for SQL Server.

Author Closing Comment

ID: 33600385
I have confirmed that it is not possible to import execution plans into the plan cache for SQL 2005 or SQL 2008, although it can be done in Oracle.  As an alternative, some shops run their most common queries with the most common parameters after a restart in order to speed up the recompilation of the plan cache.  Thank you all, for your assistance.

Expert Comment

ID: 33600493
If some of the data is static in nature, you may be able to get away with 'freezing' some plans. The obvious inherent problem with doing this, is that the plan can become stale as data changes.

With some creativity, it may be possible to have some working process that automatically removes old frozen plans, generates new plans, and freezes them. But I have never even thought of trying this before...

Plan Guides Plan Freezing

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question