物理内存,为SQL Server开启这个跟踪标记

作者: www.9159.com  发布:2019-11-20

跟踪标记:834

配置SQL Server去使用 Windows的 Large-Page/Huge-Page  allocations

目录表->页表->物理内存页

 

看这篇文章之前可以先看一下下面这篇文章

虚拟内存,物理内存,页面文件,还有任务管理器一些概念的解释

了解一下虚拟地址空间(VirtualAddressSpace,VAS)的概念

 

 

前几天在论坛里遇到一个问题

问题地址:

其中提到一个可能性的原因,开启了Large-Page allocations,看完文章之后就会知道,其实跟Large-Page allocations无多大关系

Large-Page allocations是什么来头??

参考这篇文章:Configuring SQL Server to use Windows Large-Page allocations

 

今天,一个可改变控制的实现激使我去写这篇文章,这个可改变的控制就是允许运行在Windows200864位服务器上的SQL Server 2005企业版上

使用跟踪标志834,SQLSERVER会利用 Large-Page allocations去构建内存中的Buffer Pool。

下面是我对 Large-Page allocations的理解和这个Large-Page allocations对性能的提升


页面分配

虚拟地址空间(VirtualAddressSpace,VAS)由很多的内存页面组成。这些页面有两种大小,32位系统是小的页面只有4KB大小(IA64位系统上是8KB)

64位系统上是大的页面有2MB大小(IA64位系统上是16MB)。

虚拟地址空间(VirtualAddressSpace,VAS)是由页面描述符表(简称页表)这种结构来维护的,因此,每个虚拟地址空间在页面描述符表里都有对应页面表入口

(个人感觉类似于系统服务描述符表SSDT ,内核描述符表GDT/LDT)。硬件利用这个表将这些入口转译出可读的格式,无论什么时候转译一个虚拟地址,

都必须去这张表找虚拟地址的入口。为了加快查找表上这些入口的速度,CPU维护了一块缓存叫做:Translation Look-Aside Buffer (TLB)

TLB的工作方式类似于SQLSERVER的执行计划缓存,只要一个入口曾经被转译过下次就不需要再次转译,大页的好处是提高TLB命中率,减少内核cpu消耗。

 

知道这个,就很容易去理解大的页面和小的页面对性能的差别。当一个进程的虚拟地址空间(VirtualAddressSpace,VAS)(每个进程只有一个虚拟地址空间,

虚拟地址空间=一个进程使用的内存)是由小的内存页面构成的,那么在页面描述符表里的入口就需要相应增加,同时TLB缓存的入口数也会相应增加。

举个例子,有几个虚拟地址空间需要被缓存,使用小页面(一个页面4KB)就需要在TLB中缓存更多的入口。更多的入口意味着无论什么时候接收到

一个新的转译请求,TLB的入口缓存就需要更多去循环利用。因此使用大页面去分配虚拟地址空间在性能上会有一定的提升。

www.9159.com 1

 

大的页面2MB大小,小的页面4KB大小,比如一个进程需要8MB内存,那么需要4个大页面或者2000个小页面

所以使用小页面来分配虚拟地址空间,就需要在TLB缓存更多的入口,但是TLB缓存的大小是有限的!!

 

 

页面分配和SQLSERVER

就像我早前提到过的跟踪标志834,834跟踪标志会强迫SQLSERVER进程虚拟地址空间使用大页面去构建Buffer Pool。这个跟踪标志

只能在64位SQLSERVER企业版,并且需要开启 Lock Pages in Memory功能才能使用。

当SQLSERVER使用大页面分配的时候,在SQL服务启动时入口信息会被记录在SQL ERRORLOG里

Large Page Extensions enabled.
Large Page Granularity: 2097152
Large Page Allocated: 32MB
Using large pages for buffer pool.
10208 MB of large page memory allocated.

如果SQLSERVER帐户没有Lock Pages in Memory的权限,就会在SQL ERRORLOG里记录下错误信息

Cannot use Large Page Extensions: lock memory privilege was not granted.

这篇文章非常友好地阐述了大页面分配和阐述了需要使用跟踪标志834 并使用64位SQLSERVER企业版才能使用大页面分配


 

注意:(1)不能在SQLSERVER运行的过程当中指定跟踪标志834

DBCC TRACEON(834,-1)

正在忽略跟踪标志 834。该跟踪标志无效,或者只能在服务器启动过程中指定。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

只能在SQLSERVER启动的时候加上启动参数:834

 

(2)如果SQLSERVER的《max server memory》选项被设置为0,当SQLSERVER启动的时候会占用整个服务器的所有物理内存

(3)服务器需要有8GB或以上的物理内存操作系统并且必须是企业版才能使用大页面分配

(4)可以使用下面SQL语句查看当前大页面分配的内存

SELECT  large_page_allocations_kb ,
        locked_page_allocations_kb
FROM    sys.dm_os_process_memory

www.9159.com 2

(5)如果系统符合大页面分配,并且开启了834跟踪标志,那么当SQLSERVER启动的时候会比较慢,可能需要几分钟时间

2009-06-04 14:20:31.14 Server      Large Page Allocated: 32MB 
2009-06-04 14:20:40.03 Server      Using large pages for buffer pool. 
2009-06-04 14:27:56.98 Server      2048 MB of large page memory allocated.

上面的时间显示SQLSERVER用了7分钟时间来分配页面

 

 

总结:

为什麽SQLSERVER启动的时候就占用了服务器的所有内存,其中的一个原因有可能是你开启了跟踪标志834符合SQLSERVER使用大页面分配虚拟地址空间的要求,并且设置《max server memory》选项为0

 

 

相关文章

SQL Server and Large Pages Explained….

www.9159.com, 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

 

2014-5-25补充

SQLSERVER2012新的内存管理器支持分配所有的大小。single page allocator和multi-page allocator都将退出历史舞台

不论分配多大的内存大小,内存管理器都只有三种不同的内存模型:

1、普通页

2、锁定页(locked pages,意思即是说通过设置组策略锁定内存页功能,把页面锁定在内存)

3、大内存页(large pages)

 

上面3种页面在SQLSERVER启动的时候ERROR LOG里都会打印出来,当前使用的是哪种模式的内存

 

大内存页的使用只存在于64位版的SQL Server中

1、新的内存管理器是完全支持NUMA的

2、虚拟地址空间的管理是完全动态的,特别是对32位的实例也是如此

3、内存管理器分配出去的内存都受max server memory控制

4、32位实例不再支持AWE

32位的SQLSERVER2012只能使用4GB物理内存,如果你需要使用更多的物理内存,必须要升级到64位版本

www.9159.com 3

 

实际上,跑在Linux上的Oracle也有这个问题,在Linux上开启huge page/large page的方法

1、/etc/sysctl.cnf 中增加vm.nr_hugepages参数来为大页设定一个合理的值,值的单位为2MB,需要重启Linux

2、echo 一个值到/proc/sys/vm/nr_hugepages中也可以临时性的对大页进行设定

具体参考:

 

 

 

本文版权归作者所有,未经作者同意不得转载。

from:

功能:

SQLOS's memory manager consists of several components such as memory nodes, memory clerks, memory caches, and memory objects. Fig 1. depicts memory manager components and their relationship:

在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):是一个内存管理单元,用于改进虚拟地址到物理地址转换速度;

                             | Memory Object | 

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

                              ----------------         

如何开启?

                                        |
                                        |
                                        V
                              ----------------  

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

                             | Page Allocator | 

  1. 打开SQL Server Configuration Manger;

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

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

                              ----------------         

www.9159.com 4

                             /                   

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

  2. 开启成功的话,在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的服务器上,也不建议开启这个跟踪标记;

           ---------------                  ---------------  

 

          | Memory Clerk |                 |     Caches    |

跟踪标记:845

           ---------------                  --------------- 

功能:

                                                  /   

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

                               /                /

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

                             ----------------  

www.9159.com 5

                             | Memory Node | 

从SQL Server 2012起,如何开启LPIM?

                             ----------------         

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

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

               Fig. 1

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

 

www.9159.com 6

 

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

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

Memory Node

备注:

A memory node is not exposed to memory manager clients. It is internal SQLOS's object. The major goal for a memory node is to provide locality of allocation. It consists of several memory allocators. There are three major types of allocators. The first type is a set of page allocators. The second type is virtual allocator leveraging Windows VirtualAlloc APIs. The third type is a shared memory allocator which is fully based on Window's file mapping APIs.

  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;

 

 

The page allocators are the most commonly used allocators in SQLOS memory manager. The reason they are called page allocators is because they allocate memory in multiple of SQLOS's page. A size of a page is 8k, the same as a size of database page in SQL Server. As you will learn further this is not coincidence.

注意:

 

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

There are four different types of page allocators. Single page allocator, multi page allocator, large page allocator and reserved page allocator. Single page allocator can only provide one page at the time. Multiple page allocator, as you might guess, provides a set of pages at the time. Large page allocator could be used to allocate large pages. SQLOS and SQL Server use large pages to minimize TLB misses when accessing hot data structures. Currently large pages are supported only on IA64 or x64 hardware with at least 8GB of RAM. A size of a large page on IA64 is 16MB. The last type of page allocators reserved could be used to allocate special set of pages reserved for emergency, i.e. when SQLOS is low on memory. Fig2. shows the memory node and its allocators.

  1. 可以通过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

 

 

 

参考:

           ----------------------              ----------------               ---------------------------
          | Large Page Allocator |<--------| Memory Node |--------->| Reserved Page Allocator  |
           ---------------------           /   ----------------             ---------------------------
                                                 /               |               

Trace Flag 834 and When to Use It

                                               /                 |                 

                                             /                   |                   

 

                                           /                     |                     

Tuning options for SQL Server when running in high performance workloads

                                         /                      /                     /

                     --------------------     ----------------------     ----------------------         
                    | VM & SM Allocator |    | Single Page Allocator |    | Multi Page Allocator |
                    --------------------      ----------------------      ---------------------- 
           Fig. 2

 

 

SQL Server and Large Pages Explained

At this point SQL Server doesn't have a dmv, dynamic management view, that would dump a set of all memory nodes and information about their allocators. Dbcc memorystatus, discussed further, comes pretty close but it dumps information about cpu nodes not about memory nodes. You might remember that CPU nodes are proper subset of memory nodes. It means that information presented by dbcc memorystatus is sufficient to understand memory distribution on the system.

 

 

 

Server Memory Server Configuration Options

Memory Clerks

Memory nodes are hidden from memory manager users. If a client of memory manager needs to allocate memory it first creates a memory clerk. There are four types of memory clerks such as generic, cache store, user store and object store. The latter three a bit convoluted. Along with memory clerk functionality they provide data caching.

 

 

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

One can think of a memory clerk as a bag of statistics. It supports the same type of allocators as memory nodes as well as it enables large memory consumers to hook into memory brokerage infrastructure. (I will describe infrastructure in one of the next posts). There are several global memory clerks provided by SQLOS. SQLOS's middle and large memory consumers are encouraged to use their own clerk so that one could understand memory consumption by a component. Memory clerks infrastructure enables us to track and control amount of memory consumed by a memory component. Each CPU node has a list of memory clerks that we can safely walk during runtime. SQL Server implements sys.dm_os_memory_clerks dmv to dump clerk information. In addition combined clerk information could be derived from dbcc memory status.

 

 

Memory objects

DBCC TRACEON - Trace Flags (Transact-SQL)

SQLOS's memory object is a heap. A memory object requires a memory clerk to allocate its memory. We support three types of memory objects. A variable memory objects is a regular heap. An incremental memory object is a mark/shrink heap. This allocation policy is very handy during compilations and execution processes. Usually both of the processes happen in two phases. First phase is to grow memory usage and the second is to shrink memory usage. If the process is isolated we don't have to call any of destructors when freeing memory. It significantly improves performance. The last type of memory object is fixed size. As you can guess components can use such policy when they need to allocate objects of a given size.

A payload for a given memory object is 8kb. It is exactly the same as a SQLOS's page size. It also means that a memory object could be created from memory clerk leveraging single page allocator. (This is yet another very important point! Keep this in mind until I will cover SQL Server's Buffer Pool) SQL Server exposes a dmv to dump all memory objects in its process: sys.dm_os_memory_objects.

 

 

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

If you notice both memory clerks and memory objects dmvs expose page allocator column. Also I depicted page allocator in Fig.1. Under the hood memory object uses memory clerks's page allocator interface to allocate pages. This is useful to know when you want to join memory clerk and memory object dmvs.

 

So far I have described how SQLOS's memory manager structured inside. Now it is time to start talking how all this fits into SQL Server.

 

Buffer Pool

Now we got to the point where the life becomes very interesting. In this part all the pieces that I covered so far including memory management should start fall in their places.

 

Remember SQL Server has two memory settings that you can control using sp_conifigure. They are max and min server memory. I am not sure if you know but these two setting really control the size of the buffer pool. They do not control overall amount of physical memory consumed by SQL Server. In reality we can't control amount of memory consumed by SQL Server because there could be external components loaded into server's process.

 

When SQL Server starts, during initialization, Buffer Pool first decides how much of VAS it needs to reserve for its usage. It bases its decision on the amount of physical memory, RAM, present on the box. If amount of physical memory is equal or larger than amount of VAS it can use, remember that VAS is limited resource especially on x86, it will leave 256MB of VAS for external components plus a number of threads SQL Server is configured to use multiplied by 512KB. You might remember that 512KB is SQL Server's thread stack size. In default configuration with physical memory larger than 2GB, Buffer Pool will leave 256MB+256*512KB = 384MB of VAS space. Some people name this region as MemToLeave but in reality it is in correct. SQL Server might end up using this part of VAS itself and I will show you how it could happen latter on. You might also remember -g parameter that some people recommend to use when SQL Server starts outputting "Can't Reserve Virtual Address Space" errors. First 256MB is exactly what -g parameter controls. If you specify -g 512MB, amount of VAS that BP won't use is 512MB+256*512KB = 640MB. There is no point in specifying -g 256MB. This input parameter is the same as default value.

 

Once BP decides amount of VAS it will use. It reserves all of it right a way. To observe such behavior you might want to monitor SQL Server's virtual bytes from perfmon or you could use vasummary view I talked about in my previous posts. In normal case Buffer Pool can't get this much memory in one chunk so if you take a closer look at SQL Server's VAS you will see several large regions reserved. This behavior is very different from many other servers that you might have seen. Some people report it as a VAS leak in SQL Server. In reality this behavior is by design.

 

Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can't exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.

 

The size of SQL Server database page is 8KB. Buffer Pool is a cache of data pages. Consequently Buffer Pool operates on pages of 8KB in size. It commits and decommits memory blocks of 8KB granularity only. If external components decide to borrow memory out of Buffer Pool they can only get blocks of 8KB in size. These blocks are not continues in memeory.  Interesting, right? It means that Buffer Pool can be used as underneath memory manager forSQL Server components as long as they allocate buffers of 8KB. (Sometimes pages allocated from BP are referred as stolen)

 

Here is where SQLOS and Buffer Pool meet. See Fig.3

 

                -----------------
                | Memory Node   |
                -----------------
                          |
                          |
                          V
           ------------------------
           | Single Page Allocator  |
           ------------------------
                          |
                          |
                          V
                -----------------
                |   Buffer Pool    |
                -----------------

 

 

Fig. 3

 

SQLOS' memory manager can be dynamically configured to use specific single page allocator. This is exactly what SQL Server does during a startup it configures Buffer Pool to be SQLOS's single page allocator. From that point on all dynamic single page allocations are provided by Buffer Pool. For example remember that memory object's payload is 8KB. When a component creates a memory object the allocation is served by SQLOS's single page allocator which is BP.

 

When describing the memory manager I mentioned that every large component has its own memory clerk. It means that Buffer Pool has its own memory clerk as well. How is it possible, BP leverages SQLOS memory clerk but SQLOS' memory manager relies on BP? This is common chicken and egg problem that you often can observe in operating systems. The key here is that Buffer Pool never uses any type of page allocator from SQLOS. It only leverages Virtual and AWE SQLOS's interfaces.

           -----------------
          |    Buffer Pool    |
          -----------------
                    |
                    |
                    V
     --------------------------
     | Memory Clerk (VM/AWE) |
     --------------------------
                    |
                    |
                    V
          -----------------
          | Memory Node   |
          -----------------

Fig. 4

 

All SQL Server's components optimized for 8KB allocations so that they can allocate memory through SQLOS's single page allocator and consequently through Buffer Pool. However there are cases when a component requires large buffers. If it happens allocation will be either satisfied by memory node's multi page allocator or by virtual allocator. As you might guess that memory will be allocated outside of Buffer Pool. This is exactly why I don’t like term MemToLeave, SQL Server does allocate memory out of that area!

 

 

Buffer Pool and AWE mechanism

When describing SQLOS memory manager and Buffer Pool, the discussion would be incomplete without describtion of how AWE fits in all of this. It is really important to understand how Buffer Pool allocates its memory when SQL Server configured to use AWE mechanisms. First, please remember, BP leverages SQLOS's memory clerk interfaces to allocate both VAS and physical pages through AWE. Second, there are several differences that you need to keep in mind. First BP reserves VAS in 4MB chunks instead of "single" large region. This enables SQL Server to release VAS when process is under VAS pressure. (We didn't have all bits and pieces to do this when server is not configured to use AWE mechanisms). Then it allocates all of its memory using AWE mechanism on demand. This is very big difference between SQL2000 and Yukon. In SQL Server 2000 BP would allocate all of its memory when using AWE mechanism right a way.

 

Buffer Pool is a preferred memory allocator for the whole server. In AWE mode it allocates its memory leveraging AWE mechanism. It means that all allocations allocated through SQLOS's single page allocator will come from pages allocated through AWE. This is what many people really missing. Let me make the point again: When Server is configured for AWE mode, most of it allocations are allocated through AWE mechanism. This is exactly the reason why you won't see private bytes and memory usage growing for SQL Server in this mode.

 

Since data pages are use relative addressing, i.e. self contained, Buffer Pool can map and unmap them into and out of process's VAS. Other components could have done the same if they were not relying on the actual allocation address. Unfortunately there are no components right now other than BP that can take advantage of AWE mechanism.

 

Future posts

I haven't completed discussion about SQLOS memory management yet . There is still much to talk about. In my next posts I will cover SQLOS caches and handling of memory pressure. It is also really important to look at dbcc memory status and related dmvs.

本文由9159.com发布于www.9159.com,转载请注明出处:物理内存,为SQL Server开启这个跟踪标记

关键词: