We help IT Professionals succeed at work.

How to extend lifetime of query cache for SQL 2005?

ccleebelt
ccleebelt asked
on
261 Views
Last Modified: 2012-06-27
Hello,

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?

Thanks!



Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
ccleebeltPresident

Author

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?

Thanks!




AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
-add more memory to the server
ccleebeltPresident

Author

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

Commented:
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:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx



Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.