?
Solved

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

Posted on 2010-09-02
7
Medium Priority
?
345 Views
Last Modified: 2012-05-10
Problem:
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?
0
Comment
Question by:SQLShark
[X]
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
7 Comments
 
LVL 5

Assisted Solution

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

http://www.mssqltips.com/tip.asp?tip=1749
0
 
LVL 2

Author Comment

by:SQLShark
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?
0
 
LVL 5

Expert Comment

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

Scratching head...
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 2

Author Comment

by:SQLShark
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.
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1000 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.
0
 
LVL 2

Author Closing Comment

by:SQLShark
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.
0
 
LVL 5

Expert Comment

by:almander
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
http://blogs.msdn.com/b/sqlblog/archive/2009/02/19/plan-guides-plan-freezing-in-sql-server-2005-2008.aspx
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

719 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