SQL Server 2008 - Save Single Query Plan Cache so Re-compilation is not necessary


I am running SQL Server 2008 and have a query that takes about 10 seconds to load the very first time... but then subsequent runs of that query take only 1 second or so.  I believe this is due to query plan caching.  When I execute sub-sequent runs of the query using the "DBCC FREEPROCCACHE" statement first, then it takes 10 seconds each time.

Anyways, I have a website which this query is executed on and I was wondering how I can go about saving that query execution plan so that it doesn't need to figure it out for the first visit to that page each time.  All other queries on the site load quickly.  Is it possible to save a query plan and not have it removed from cache?

Thanks in advance!
Who is Participating?
Have you tried creating an indexed view and querying?
Test it and see how it goes...

Something of that kind was discussed here
Alpesh PatelAssistant ConsultantCommented:
SQL server first check for exiting plan for query. If matched then use existing otherwise create new plan and cache that.
adrian78Author Commented:
Thanks for the replies!

LionKing -  I'll look into Indexed Views.
Patel - Yes, this is how plan caching works but then it disappears from the cache.  How do you prevent the plan from disappearing from the cache?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Is the entire proc cache disappearing or just this plan?
Scott PletcherSenior DBACommented:
Most likely you didn't qualify the table names with the schema name.  In that case, SQL can't cache the plan and use it for others.

For example, is your query like this:
FROM tablename

Or this:
FROM dbo.tablename

If it's like (1), SQL can't reuse the plan for that query.
adrian78Author Commented:
JAT-DBA - I believe it's just this plan that's not caching.  
Scott - Thanks for the info regarding adding the fully qualified schema name.  I'll give that a go and let you know what happens!
adrian78Author Commented:
Hi Guys - I've looked into this a bit and I think the indexed view will be the best approach.  Fully qualified schema names are helpful as well.  Thank you!
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.

All Courses

From novice to tech pro — start learning today.