One of SQL Server expert recently asked below question in distribution list
1. LPIM is not recommended?
2. LPIM is not required?
Answer:
When we have LPIM privilege for SQL Server startup account AWE allocator API’s are used for memory allocation. Memory allocated using AWE allocator API’s are not part of Process working set ,hence cannot be paged out and not visible in private bytes or working set in task manger and Perfmon àPrivate bytes or Perfmon àworking set.
Advantage: SQL Server working set (BPOOL) cannot be paged by windows even when there is system wide memory pressure.
Disadvantage: Operating system will starve for memory when there is system wide memory pressure. OS has to completely rely on SQL Server to respond to low memory notification and scale down its memory usage . SQL Server may not respond fast enough to low memory condition at system level because OS is already starving for memory. LPIM prevents only the BPOOL from paging, Non-Bpool components can still be paged and we have critical portions of SQL Server memory like thread stack, SQL Server Images/DLL’s in Non-Bpool which can still be paged by OS.
So many disadvantage…. But still why do we recommend LPIM in some places?
In earlier versions of windows 2003 when there is system wide memory pressure windows memory manger would trim one-quarter of working set of all the process. Imagine If SQL Server is using 200GB of RAM and there is system wide memory pressure, Windows memory manager would move 50 GB of
SQL Server working set to page file and we would end with performance problems. If LPIM is enabled OS cannot trim.
Imagine there is a faulty application in the server and it leaks memory fast , It might consume all the memory in the server and windows memory manager might trim all of SQL Server working set.
Known issues in windows like the one in This link can cause windows memory manager to trim the working set of SQL Server process suddenly. Windows has a background process which keeps writing the contents of working set to page file, so when there is paging only the dirty pages needs to be moved to the page file others are already backed by back ground process, So paging would be very fast and SQL Server working set would be moved to page file in seconds before SQL Server responds to low memory resource notification from OS causing negative performance. This link might throw more clarity.
In systems with large amout of memory (Ex: 1 TB )we might get non yielding scheduler situations when allocating memory in conventional memory model. LPIM is only option is this case.
LPIM can be used in servers in which it might take long time to identify the cause of the working set trim. It is always suggested to identify the cause of TRIM before choosing LPIM in first place.
SQLServerScribbles.COM 