1 SQL Server Large Page

SQL Server is also the windows application. The internal memory allocation is calling the standard windows API. The normal page size for windows memory is 4KB on 64-bit windows OS. To make the memory management more efficiency, we can enable the large page allocation for SQL server. The large page is 2MB. 

To enable the large page, here are some prerequisite needs to be meet first: 

 

• SQL Server enterprise  Edition
• Host has at least 8GB or more ram.
• LPIM privilege is set for the user account

There is another trace flag 834. In MSFT KB article, 834 is to enable the large page for buffer pool. It is one of the approach when tuning the SQL Server performance in large data warehouse environment.

Lets take a look those settings and how it show up for SQL server.  

Large Page allocation with trace flag 834

We can see lock page message in the SQL server error log.

In 2008R2 , without the trace flag 834

2013-11-16 05:57:16.04 Server      Large Page Extensions enabled.
2013-11-16 05:57:16.04 Server      Large Page Granularity: 2097152
2013-11-16 05:57:16.05 Server      Large Page Allocated: 32MB
2013-11-16 05:57:16.50 Server      Using locked pages for buffer pool.

select large_page_allocations_kb,locked_page_allocations_kb from sys.dm_os_process_memory

select type,awe_allocated_kb,page_size_bytes from sys.dm_os_memory_clerks

where type like 'MEMORYCLERK_SQLBUFFERPOOL' and memory_node_id=0

select bpool_committed,bpool_commit_target,bpool_visible from sys.dm_os_sys_info

select * from sys.dm_os_performance_counters

where object_name like '%Memory%'

0 LPIM again

We know LPIM privilege is required for LPIM and large page for SQL server but how does SQL server know your account has the privilege set in the local security policy.

The answer is AdjustTokenPrivileges

 

I have not find the way to determine the value for the function return. The same call stack is present whether the LPIM privilege is set or not . This is reasonable because either way, SQL server needs to check the privilege.

The another difference shows in the error log.

When LPIM is enable :

2013-11-04 20:15:24.71 Server      Using locked pages in the memory manager.

When LPIM is not enable:

2013-11-04 20:30:44.16 Server      Using conventional memory in the memory manager.

When Host has more than 8GB memory, Enterprise edition SQL server and LPIM privilege. 

2013-11-04 20:50:20.91 Server      Using locked pages in the memory manager.
2013-11-04 20:50:21.31 Server      Large Page Allocated: 32MB