What Does Lock Pages in Memory Do

I thought I would post some clarification on what the famous "Lock Pages in Memory" policy setting does and does not do.

1. It prevents the operating system from stealing the buffer pool (see previous blog on buffer pool), thus shrinking the working set.

2. It is NOT available on Standard Edition.

3. It ALSO enables an enterprise feature called "Large Page Extensions" on 64 bit systems. This allows SQL Server to allocate memory in 4-16MB chunks. This is order of magnitudes faster than the piddly default 4k size and highly recommended for processing huge queries.

3. You can tell if Lock Pages in Memory is enabled by looking in the sql server log for the entry "Large page extensions enabled" in the log.

4. It does not stop the operating system from shrinking the working set. The Thread and MemToLeave areas can still be bashed causing pressure. (see previous blog).

5. It is automatically enabled on 32 bit systems if AWE is used. As we said earlier. AWE sucks, so no big gain here ;-)