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.