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

Posted on 2012-09-19
Last Modified: 2012-10-04

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!
Question by:adrian78
    LVL 13

    Accepted Solution

    Have you tried creating an indexed view and querying?
    Test it and see how it goes...

    Something of that kind was discussed here
    LVL 21

    Expert Comment

    by:Alpesh Patel
    SQL server first check for exiting plan for query. If matched then use existing otherwise create new plan and cache that.

    Author Comment

    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?
    LVL 5

    Expert Comment

    Is the entire proc cache disappearing or just this plan?
    LVL 68

    Assisted Solution

    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:
    SELECT ...
    FROM tablename

    Or this:
    SELECT ...
    FROM dbo.tablename

    If it's like (1), SQL can't reuse the plan for that query.

    Author Comment

    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!

    Author Comment

    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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now