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

How to extend lifetime of query cache for SQL 2005?


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?


1 Solution
put it in a view so that it is pre-compiled
ccleebeltPresidentAuthor Commented:
> 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?


Aneesh RetnakaranDatabase AdministratorCommented:
-add more memory to the server
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

ccleebeltPresidentAuthor Commented:
> -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.

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:

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

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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