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%'