Lock page in memory setting in SQl 64 system

Hi All,

We know that locking page in memory setting is recommended to set in SQL 64 system. The purpose is used to avoid memory pages in Sql address will not be OS paged out.

It is a little confused that if the memory pages of sql cannot be paged out after setting lock page in memory. Then the memory used by SqL will increase forever. If the physical memory is limited, it will obviously caused the memory pressure of the system eventually. How does the 64 bit sql manage his Buff pool in such scenario? Will it be paged out? Thanks in advance

SQL Server listens to memory notifications from windows. These notifications indicate the state of physical memory on the system, low (meaning that the system does not have enough memory to satisfy new allocations) or High (meaning there is an abundance of memory available).

When the low memory notification is set SQL Server reduces memory usage until the notification goes off, thereby avoiding the scenario you describe.

To answer your question about whether locked pages will ever be paged out - no, locked pages cannot be paged out by the OS. If no more physical memory is available new allocations will simply fail.

|||

Thanks. you mentioned "when the low memory notification is set SQL Server reduces memory usage." However, (in 64 bit environment), since the page is locked in memory at that time, how did SQL Server reduce the memory usage?

Do you mean that the SQL will report error if the sql server is in such scenario (low physical memory in OS but heavy workload in SQL requests more memory) ?

|||Locking a page does not mean you cannot deallocate it. The application that allocated and locked the page is free to deallocate it at any time.|||Thanks a lot !!|||You also can and should set the max memory setting in SQL Server 2005 to a level that reserves some memory for the OS. Slava Oks has blogged about the recommended settings for this. Hopefully, you are running SQL Server 2005 on a dedicated server, so its not competing for memory with any other applications.