November 16, 2010 at 9:31 pm
I am trying to get some more info on procedural cache. I have few questions:
i) How often does procedural cache gets cleared? How much space is allocated for it?
ii) How to find total cache of the sql server?
Thanks
November 17, 2010 at 4:29 am
This article has some good information.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 17, 2010 at 4:37 am
November 17, 2010 at 4:41 am
I'm sorry. Updated it now.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 17, 2010 at 4:43 am
The procedure cache only gets completely cleared if you request it (DBCC FREEPROCCACHE) or if certain server configuration changes occur (some of the sp_configure options) or for certain other server maintenance type operations.
Individual plans will remain in cache until either they are aged out of cache due to lack of use or until they are considered invalid (such as by a alter procedure). They may also be recreated (recompiled) if they are considered non-optimal or invalid.
The amount of space allocated to the plan cache is not a fixed number. It depends on the total memory available to SQL and it's a soft limit, not an absolute size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2010 at 4:50 am
Adiga (11/17/2010)
This article has some good information.
That article is seriously out of date and a lot of the information in there is not valid for SQL 2005 or above.
A far better and far more accurate article would be the Microsoft whitepaper on plan caching and plan reuse in SQL 2005.
http://technet.microsoft.com/en-us/library/cc966425.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply