Further explanation of SQL memory use

One of the comments on the last blog entry I wrote (Memory Use in SQL Server) asked a few questions in order to further clarify some of the finer details of memory use in SQL Server. Here is the comment:

I have a question about SQL 2005 ent edition on system with 16GB of RAM. Its paging out memory every 2-3 days and microsoft's suggestion is to lock pages in memory so no paging could occur. At the same time it says that it will stay dynamic and it will release it to OS when needed.
Questions are:
Is MAX SQL Server memory setting necessary when having 'lock pages in memory' on?
What is the difference between paging and releasing to OS on demand?
What happens if I set MAX SQL server memory to 14Gb out of 16Gb and SQL server reaches 14GB. Will it start showing out of memory errors or will it start committing it's changes in the memory and reusing it for itself?

Thanks
Duke N.

I started writing a response but it took on a life of its own and before I knew it it was really too big for a comment on a blog entry and was worthy of a blog entry of its own (well, at least in size anyway). So here is my response...

Hmmm...well first, it should be noted that I don't work for Microsoft and that if they have given you some advice then you should probably follow it. That said, this is how I understand the areas you asked about:

1) "max server memory" and "lock pages in memory" are two unrelated, or at least very loosely related, independent settings. "max server memory" is a SQL setting that is configured with the cataloged stored procedure sp_configure and sets the maximum size for the SQL Server process's virtual address space. "lock pages in memory" is an OS permission that is configured with Windows local policy editor (and the Group Policy editor, I think - I try to stay away from GPO personally if possible); when granted to a user account it allows processes that run under the context of that account to lock pages in physical memory so that they are not paged out to disk (ie. to virtual memory). The two settings can, and often do, work independent of each other. So, in answer to your question, yes, "max server memory" is still necessary (if you want to limit the size of the SQL Server process's virtual address space) regardless of the "lock pages in memory" permission. One does not imply the other.

2) Paging is generally defined as the process of moving pages out of physical memory to virtual memory (backed by slower hard disk) when there is not enough physical memory to satisfy the last memory allocation request. When SQL Server releases memory back to the OS, "on demand" as you put it, it is reducing the amount of memory is has committed (so it is not hogging as much memory as before), which generally means the data buffer cache, where data pages (index & table pages) are cached, reduces in size. If you were to query the OS via WMI, perfmon or task manager, it would tell you that SQL Server is using less memory than before. That doesn't mean there will be more free memory, there almost certainly won't, since SQL Server is only releasing memory because some other app is asking for it.

3) If you set "max server memory" to 14GB then SQL Server will continue committing memory until its virtual address space is 14GB. What happens next depends on several things - it's different for SQL 2000 vs SQL 2005 & Windows 2000 vs Windows 2003 & x64 (64 bit) vs x86 (32 bit) architecture. You've said SQL 2005, so I'll ignore SQL 2000. With SQL 2005, if you're running x64 then it uses a flat addressing model and it acquires & releases memory dynamically (just like x86 processes under the 4GB limit), without the need for AWE memory (AWE memory is only needed as a kludge to address memory over the 4GB limit imposed by the 32-bit architecture). However, with SQL 2005 (x86) the memory behaviour is slightly different depending on whether it's running on Windows 2000 or Windows 2003. With Windows 2003, SQL 2005 can support dynamic allocation (and release) of AWE memory (ie. a process's virtual address space over the x86 4GB limit). With Windows 2000, if configured to use AWE memory (ie. sp_configure "awe enabled" is on, and there is enough physical memory to allow it) then SQL will use that AWE memory. Now, with Windows 2000, AWE memory cannot be swapped out. Once it is committed in physical RAM, those physical memory pages will stay committed to the SQL Server process until it is shutdown (ie. SQL Server is stopped with a NET STOP command). This is why the service account running the SQL Server process needs the "lock pages in memory" permission, so that it can use this AWE memory. In this case, the OS may get starved for physical RAM and have to service more memory requests with virtual RAM (you'd get the usual warnings & errors that Windows issues when there is no free physical memory and the page file needs to expand). In all other cases I would expect the SQL Server process to simply release memory when asked to, unless it has dropped down to the "min server memory" limit (which defines at which point SQL Server will stop dynamically releasing memory). In the case of AWE memory, that would reduce the size of the data buffer cache (because only the data buffer cache uses AWE memory).

I hope that answers your questions. I have one now: when you say it is "paging out memory every 2-3 days" what do you mean exactly? That every few days there is suddenly a lot of "paging" happening, or that every few days SQL Server suddenly releases a lot of memory? I don't think allowing the account running the SQL Server process to lock pages in memory (with the "lock pages in memory" permission) will force SQL Server not to release memory. If I wished to stop SQL Server from releasing memory I would set a "min server memory" setting so that once it exceeds that amount it will not release memory if that would force it to fall below that limit. I would use "min server memory" and "max server memory" to manage the dynamic memory management thresholds and I would grant the "lock pages in memory" permission to the service account if it was required to use AWE memory (I was under the impression that the SQL Server 2005 installation already managed the "lock pages in memory" permission, but it's been a while since I looked at this so I could be mistaken).

Here endth more comment. Too long for a comment? Yeah, I thought so too.