Monday, July 16, 2007

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?

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.


At 23/8/07 18:58, Blogger Anders said...

Lovely article, again. I'm dealing a lot with both 32 and 64 bit systems now and I really appreciated the table with recommendation for 32bit:
RAM /3GB /PAE 'awe enabled' 'max server memory'
0-3 Not necessary
>3-4 * Not necessary
>4-12 * * * (Physical RAM) - (RAM needed for other applications & OS)
>12 * * (Physical RAM) - (RAM needed for other applications & OS)

What about 64 bit and >4GB? Are modifications to boot.ini still necessary?

Thank you

At 24/8/07 09:34, Blogger Mike Hodgson said...

Thanks Anders.

The beautiful thing about 64-bit is that the whole memory addressing problem goes away. No more need for PAE or the AWE API. x64 can address memory right up to the OS maximum with no kludge APIs. See the table about half way down this page:
Memory Architecture

At 24/8/07 16:57, Blogger Anders said...

Thanks Mike, finally it's all clear to me now :-)

At 18/10/07 04:53, Anonymous Jeff said...


I've been reading many posts about SQL Memory "How To's & How Not's" and have been unable to find a solution to my problem. I am running a 2-node cluster running SQL2K5 Std. 64-bit on WIN2K3 Ent. 64-bit. I have 16GB of RAM and have set the Min Memory = 1GB & Max Memory = 13GB. I originally turned on the Lock Pages in Memory setting, but later found out, that feature doesn't work in SQL2K5 Std. 64-bit editions and thus my SQL Server keeps Paging out disk. In particular, when the instance runs on 1 node, I start seeing application issues after 3 days and the SQL Process has eaten up all 13GB's. The SQL log has events about Paging to disk. Once I fail the instance to the other server, the problem doesn't come back, though it also shows 13GB's used for the SQL process, no application issues occur. The servers are identical, so I am a little confused. I am hoping to just prevent SQL from paging out to disk, so if you know of any other options for SQL2K5 Std. 64-bit to lock pages in memory, I would appreciate it.



At 6/1/08 17:37, Anonymous Anonymous said...

Thanks for the nice info

At 7/1/08 02:22, Anonymous Anonymous said...

Great article indeed

At 22/2/08 13:59, Blogger Mike Hodgson said...

Jeff, firstly sorry for not getting back to you a few months ago when you asked (I've been largely neglecting my blogs, which is probably all too obvious).

To answer your question, 'lock pages in memory' still works with SQL 2005 x64, but it's not quite so apparent (as with x86). If you grant the service account the right to lock pages in memory, the SQL Server will lock pages in memory and won't be forced to give it up to other processes, even in x64. The thing that's a bit weird about it though is that the allocated memory (locked in physical RAM) won't show up in task manager or the normal perfmon counters. But it can still be seen with perfmon counters such as SQLServer:Memory Manager | Total Server Memory (KB).

One big problem with this though is that if some other app needs the memory you're fresh out of luck - SQL Server has commited it and won't give it back; period. For example, if you have 2 SQL instances on your cluster and one fails over so they're both running on the same node, the 2nd one to start up will have much less memory available to it to consume.

Also, one last thing, SQL server eating up all your memory is not necessarily a bad thing - that's what it's designed to do with the data cache (both I assume you already know that).

Hope this helps a bit & sorry for the delay.


At 22/2/08 14:12, Blogger Mike Hodgson said...

Also, here is a related Microsoft KB article:
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005


Post a Comment

<< Home