Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2010-09-02
7
Medium Priority
?
351 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
  • 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

595 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