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.
When enable the trace flag 834 in SQL server 2008R2 ( must in the start up time), we can see the below message in the sql server error log.
2013-11-16 05:50:24.15 Server Large Page Extensions enabled.
2013-11-16 05:50:24.15 Server Large Page Granularity: 2097152
2013-11-16 05:50:24.16 Server Large Page Allocated: 32MB
2013-11-16 05:50:24.82 Server Using large pages for buffer pool.
2013-11-16 05:50:25.52 Server 3000 MB of large page memory allocated.
- Without the 834, SQL server would only use the large page for non buffer pool.
- With 834, the memory counter “Total Server memory” does not really present the total SQL server memory usage in this case. The task manager and DMV ( sys.dm_os_process_memory) shows the SQL server memory usage
Lets take a look the same configuration within SQL Server 2012.
Without Trace flag 834, we can see below message in SQL Server 2012 error log,
2013-11-05 20:49:05.32 Server Using locked pages in the memory manager.
2013-11-05 20:49:05.32 Server Large Page Allocated: 32MB
With Trace flag 834, we can see the same message in SQL Server 2012 error log. There is NO use large page for buffer pool message any more like SQL Server 2008R2. Because the Buffer Pool is not the memory manager any more in SQL 2012, it is just the memory consumer.
2013-11-05 20:16:30.28 Server Using large pages in the memory manager.
2013-11-05 20:16:30.29 Server Large Page Allocated: 32MB
- Because Buffer pool become the memory consumer in SQL 2012, once large page condition has meet, SQL server would use large page for buffer pool anyway. From what I can see, the the trace flag 834 only make SQL Server pre allocate the memory during the start up time .
- Memory Counter ( Total Server memory) has reflect the amount of the SQL server memory is using.
Total Memory is pre allocated during the start up time in SQL Server 2012 and 2008R2 when trace flag 834 is in used.
In SQL 2012, Target Server Memory = Total Server Memory , it is not the case for SQL 2008R2 .
It is possible that SQL Server can not allocate Max Memory for the buffer pool during the start up.
In below screenshot, the SQL Server max memory is set to 7GB. With trace flag 834, SQL server would try to set the Total Server memory to 7GB but it only be able to grab close to 5GB ( 4986336KB).
If the same case happen in the SQL 2008R2, we would get the message in the error log
2013-11-05 21:13:56.03 Server Large Page Extensions enabled.
2013-11-05 21:13:56.03 Server Large Page Granularity: 2097152
2013-11-05 21:13:56.04 Server Large Page Allocated: 32MB
2013-11-05 21:13:56.13 Server Using large pages for buffer pool.
2013-11-05 21:13:56.72 Server 3880 MB of large page memory allocated.
The Max memory is no longer dynamical. Even we increase or decrease the max memory, the Target Server memory and total server would not changed . This is same as SQL 2012, 2008R2.
- SQL Server and Large Pages Explained : http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx