[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

cache in memory

What is the default time for SQL cache to be in physical Memory?

and is it possible to increase this limit (apart from increasing memory)?

thanks
0
anushahanna
Asked:
anushahanna
  • 3
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> What is the default time for SQL cache to be in physical Memory?

Its an algorithm named MRU / LRU based and not based upon time..
MRU / LRU stands for Most Recently Used and Least Recently Used..

Most Recently Used queries will be held in cache whereas least recently used queries would be removed out from the cache automatically..

>> and is it possible to increase this limit (apart from increasing memory)?

No.. Its not possible to increase the limit apart from increasing memory..
An workaround is there which is DBCC PINTABLE which would force Server to keep a particular plan in cache for a longer time..

More info about DBCC PINTABLE here:

http://msdn.microsoft.com/en-us/library/ms178015%28SQL.90%29.aspx

PS: Recommended to use only if it is required and be careful with it as it might affect your buffer pool thereby reducing overall performance.
0
 
anushahannaAuthor Commented:
Thanks for the helpful info.

So, it is possible that only limited MRU is cached, if enough memory is not available, right? and we do not to worry about LRU since it is not helpful and system takes care of it?

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes.. You got it correctly..
If you have more memory with you, then you can cache more plans in your Procedure Cache..
0
 
anushahannaAuthor Commented:
Thanks for confirming it.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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