Monday, November 18, 2013

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

image

 

image

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.

image

image

image

  • 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

select large_page_allocations_kb,locked_page_allocations_kb from sys.dm_os_process_memory
 
select type,awe_allocated_kb,page_size_in_bytes from sys.dm_os_memory_clerks
where type like 'MEMORYCLERK_SQLBUFFERPOOL' and memory_node_id=0
 
select committed_kb, committed_target_kb ,visible_target_kb from sys.dm_os_sys_info
 
select * from sys.dm_os_performance_counters
where object_name like '%Memory%'

image

image

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

image

image

image

  • 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.

 

Summery

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).

image

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.

image

 

 

 

Reference

1 comments:

  1. You really did a great job. I found your blog very interesting and very informative. I think your blog is great information source & I like your way of writing and explaining the topics. Keep it up.For more details about oracle fusion financial please check our website.
    Oracle Fusion Financial Training Institute

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates