Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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!
2 Solutions
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now