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
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
  • 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...
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

740 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