How to extend lifetime of query cache for SQL 2005?

Posted on 2007-10-01
Last Modified: 2012-06-27

Running Microsoft SQL Server 2005 on Windows 2003.

We are running some fairly complex queries that only return 5 to 100 results.

The first time the query is executed it takes 3-6 seconds (depending).  The next time the same query is executed it only takes 1-1.5 seconds.

After a few minutes the cache seems to be cleared and the same query takes 3-6 seconds again.

How can we extend the lifetime of the query cache so we get the 1-1.5 second execution times for more than a few minutes?


Question by:ccleebelt
    LVL 16

    Accepted Solution

    put it in a view so that it is pre-compiled

    Author Comment

    > put it in a view so that it is pre-compiled

    Unfortunately we cannot use a view in this particular instance.  Depending on the search criteria, the query is built up piece-by-piece.

    Only several different views - one for each combination of search criteria - would work.  Plus some way to figure out which view to query!

    Any way to extend the cache timeout?


    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    -add more memory to the server

    Author Comment

    > -add more memory to the server

    Somehow 4 dual-core 3.4Ghz Xeon processors and 16Gb of RAM aren't enough?  Mkay....

    Anyone else?  Perhaps (I don't know) maybe more than a 1-line response?

    Thanks in advance.

    LVL 27

    Expert Comment

    SQL determines cache based on available memory.

    The max server memory option is the hard limit for the buffer pool size in SQL 2005. The execution plan cache is determined by the size of the buffer pool. Execution plans start with a given number, 16, for example. As soon as the size of the plan cache reaches 50 % of the buffer pool size, the next access drops all of the plans by 1. As people use the database, the cache keeps decrementing until the plan is dropped when it reaches 0.

    There are two types of cache - query plans and execution context. Query plans are the common elements of your query. Execution context are the elements that are particular to that query (parameters that return results from 5 to 100).

    There are many factors that may cause a recompilation of your complicated queries. This link explains and gives examples of them:

    LVL 30

    Expert Comment

    If you are executing dynamic SQL with variable inputs, there is a very good chance that your query will be recompiled on every execution. If this is the case, you should consider the suggestions above - using precompiled views.

    Alternatively, post your query here and we can have a look at it

    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

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    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…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now