Solved

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

Posted on 2010-09-02
7
295 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 250 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 24

Accepted Solution

by:
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.
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now