I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's dynamic memory manager.
Can someone confirm my understanding of SQL Server’s dynamic memory management is correct?
In SQL Server 2005/2008/2008R2 x64
a)
Max amount of memory taken by locks is limited to 60% of the buffer pool memory (max server memory). By disabling lock escalation and doing a huge insert I can see that SQL Server can take up to 60% of buffer pool memory before throwing error number 1204 (out of memory for locks)
b) Plan Cache size is limited to
a) Number of plans in cache -- 160K
b) Plan Cache upper limit: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB.
Once this threshold is reached SQL server will start evicting execution plans from the cache until SQL server stop seeing internal plan cache pressure
c) Workspace memory is limited to 75% of max server memory and a single query can not take more than 25% of the (available 75% max workspace memory) unless overridden by resource governor.
My question is
Is there a limit on max memory taken by database page cache before lazy writer start evicting pages out of memory? How does it controlled? By looking at free pages count in the buffer…**
How sql server controls memory taken by "transaction log cache" before flushing log to the disk?
Thanks in advance.
Best Answer
This isn't a complete answer, as I admit I don't know the complete answer, but can't fit into a comment.
Short detour - regarding SQLOS memory there is a wealth of information coming from a guy that worked on the internals of SQL Server storage team - Slava Oks. He has lots of articles on MSDN blogs regarding the SQLOS memory manager and they are all worth a read. Now I know we're on to SQL Server 2012, but the info is valuable nonetheless.
Now, regarding your first question, if there's a limit of max memory taken by db page cache before being cleared out and how is it controlled, I'll quote his word on it.
From article SQLOS's memory manager and SQL Server's Buffer Pool:
From this article's comment I understand that SQL Server uses a LRU algorithm (LRU = least recently used). If you really need to go in depth, SQL Server's LRU algorithm in particular is described in the following research paper: The LRU-K Page Replacement Algorithm For Database Disk Buffering.
Now, for your second question, the log caches are not really exposed so they can't be changed/tuned, as they are a part of the SQLOS internal memory structure. Quoting from an answer from MSDN forum: