5. 跟踪标记 (Trace Flag) 834, 845 对内存页行为的影响

跟踪标记:834

功能:

在64位的windows环境下,为SQL Server开启这个跟踪标记,那么SQL Server 会使用大页(Large pages)为内存缓冲区(buffer pool)分配内存,从而可以提高CPU转换检测缓冲区(TLB: Translation Lookaside Buffer)的效率得以提升性能;

大页(Large Pages): 正常情况下windows内存是4KB的页,而大页的最小空间是2MB,也就是说分配的时候可能大于2MB;

转换检测缓冲区(TLB: Translation Lookaside Buffer):是一个内存管理单元,用于改进虚拟地址到物理地址转换速度;

SQLOS有4种内存分配方式:单页(Single Page), 多页(Multi Page),这里的页都是SQLOS的页,同数据页大小一样为8KB,大页(Large Page), 保留页(Reserved Page);默认情况下Buffer Pool使用Single Page的分配方式,Buffer Pool之外使用Multi Page的分配方式,而834跟踪标记改变的就是Buffer Pool的分配方式。

如何开启?

跟踪标记834只能在启动SQL Server时开启

1. 打开SQL Server Configuration Manger;

2. 右击SQL Server实例选择属性(Properties);

3. 在启动参数 (Startup Parameters) 里添加-T834;

4. 保存并重启SQL Server实例的服务以生效;

5. 开启成功的话,在SQL Server errorlog里可以看到类似字样:Using large pages in the memory manager.

备注:

1. 大页 (Large Pages) 分配只在SQL Server 64位+企业版+大于8GB内存的SQLOS有效;

2. 需要为SQL Server开启了Lock Pages In Memory (LPIM);

3. 建议只在SQL Server的专用服务器上开启,否则如果内存碎片太多或者内存不足无法分配大页,可能会导致SQL Server无法启动;另外在使用columnstore index的服务器上,也不建议开启这个跟踪标记;

 

跟踪标记:845

功能:

Locked Pages In Memory(LPIM)是一个windows特性,用于控制Windows进程不使用虚拟内存;

在SQL Server 2012前,如果要对SQL Server进程开启这一特性,根据版本不同,可能会需要用到跟踪标记845,详见下表:

SQL Server 2012起,如何开启LPIM

1. 开始菜单 - 运行 - 输入gpedit.msc  - 回车,以打开组策略;

2. 计算机配置 - Windows 设置 - 安全设置 - 本地策略 - 用户权利指派

3. 双击“锁定内存中的页”,在“本地安全策略设置”对话框中,单击“添加”按钮添加SQL Server服务账号并确认;

4. 重启SQL Server服务以使配置生效;

5. 成功开启LPIM后,在SQL Server errorlog里可以看到类似字样:Using locked pages in the memory manager.;未开启LPIM的话,在SQL Server errorlog里可以看到类似字样:Using conventional memory in the memory manager.;

备注:

1. 不难发现,在SQL Server 2012前,64位的标准版里开启LPIM会可能会用到跟踪标记845,从SQL Server 2012之后就再也不需要了;

2. 如果同时开启834跟踪标记和LPIM,那么errorlog只会显示:Using large pages in the memory manager.,并不会显示:Using locked pages in the memory manager.,因为开启跟踪标记834的前提是开启了LPIM;

 

注意:

1. 是不是不开启834跟踪标记,就完全不使用大页分配?不是。在开启了LPIM的环境里,注意看errorlog就会发现,会有类似:Large Page Allocated: 32MB的字样;但如果没有开启LPIM,就不会使用大页分配;

2. 可以通过DMV查看Large page和Locked page的分配大小,在仅开启了LPIM的环境里,Locked Page Allocation会比较大,Large Page Allocation较小甚至为0;在开启了834跟踪标记的服务器上Large Page Allocation会较大,有少量Locked Page Allocation;如果834和LPIM都未开启,这两个列值均为0,脚本如下:

--开启了834跟踪标记 (errorlog: Using large pages in the memory manager.)
select large_page_allocations_kb+locked_page_allocations_kb as large_and_locked_pages_kb,physical_memory_in_use_kb,large_page_allocations_kb,locked_page_allocations_kb 
from sys.dm_os_process_memory
--large_and_locked_pages_kb    physical_memory_in_use_kb    large_page_allocations_kb    locked_page_allocations_kb
--194122560    194387600    194101248    21312

--仅开启了LPIM (errorlog: Using locked pages in the memory manager.)
select large_page_allocations_kb+locked_page_allocations_kb as large_and_locked_pages_kb,physical_memory_in_use_kb,large_page_allocations_kb,locked_page_allocations_kb 
from sys.dm_os_process_memory
--large_and_locked_pages_kb    physical_memory_in_use_kb    large_page_allocations_kb    locked_page_allocations_kb
--255167036    256325340    1718272    253448764

--两个都没开启 (errorlog: Using conventional memory in the memory manager.)
select large_page_allocations_kb+locked_page_allocations_kb as large_and_locked_pages_kb,physical_memory_in_use_kb,large_page_allocations_kb,locked_page_allocations_kb 
from sys.dm_os_process_memory
--large_and_locked_pages_kb    physical_memory_in_use_kb    large_page_allocations_kb    locked_page_allocations_kb
--0    1688278640    0    0

 

参考:

Trace Flag 834 and When to Use It

https://blogs.technet.microsoft.com/sql_server_isv/2010/11/30/temenos-t24-trace-flag-834-and-when-to-use-it/

 

Tuning options for SQL Server when running in high performance workloads

https://support.microsoft.com/en-us/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloa

 

SQL Server and Large Pages Explained

https://blogs.msdn.microsoft.com/psssql/2009/06/05/sql-server-and-large-pages-explained/

 

Server Memory Server Configuration Options

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options#lock-pages-in-memory-lpim

 

How to enable the "locked pages" feature in SQL Server 2012

https://support.microsoft.com/en-us/help/2659143/how-to-enable-the-locked-pages-feature-in-sql-server-2012

 

DBCC TRACEON - Trace Flags (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

 

SQLOS’s memory manager and SQL Server’s Buffer Pool

https://blogs.msdn.microsoft.com/slavao/2005/02/11/sqloss-memory-manager-and-sql-servers-buffer-pool/

posted @ 2018-03-01 14:14 张骞 阅读(...) 评论(...) 编辑 收藏