SQL Server Dynamic Memory management

Question

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.

Answer 1

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:

Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can't exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.

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:

Essentially each database has a log buffer/cache for its transaction log which is a small contiguous memory allocation, that is 60K in size and used by active transactions, and flushed when a transaction commits, or when the buffer space fills up. Multiple active transactions can be interleaved in the log buffer, and if any one transaction commits, the buffer memory is written to the transaction log file by the log writer. If the space fills up, the contents get flushed to disk allowing the buffer/cache memory to be reused. This is why log IO can range for 512bytes to 64K (its actually 60K), but it is always sequential so varying write sizes doesn't matter that much. The format of the log records is different than data pages as documented in the BOL (http://msdn.microsoft.com/en-us/library/ms190925.aspx), so the size of a log record is variable, but the size of the log buffer/cache is consistent.

Answer 2

Just completing the answer, i have seen in SQL Server: Logging, Recovery, and the Transaction Log course that Paul Randal recorded to Pluralsight, each database have 128 log buffers each with 60KB. So, when a log block is ended (record a commit, abort or fill up 60K) it is copied to one of 128 log buffers, than asynchronously wrote to disk, limited by outstanding I/Os.

The size still fixed but in 128 x 60K = 7680K for each database.

I hope it helps ;)