[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to extend lifetime of query cache for SQL 2005?

Posted on 2007-10-01
Medium Priority
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

SQL_SERVER_DBA earned 1500 total points
ID: 19991845
put it in a view so that it is pre-compiled

Author Comment

ID: 19991887
> 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
ID: 19991904
-add more memory to the server
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 19992084
> -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

ID: 19992445
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 29

Expert Comment

ID: 19993896
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

873 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