当前位置: 澳门新濠3559 > 数据库 > 正文

从而可以提高CPU转换检测缓冲区(TLB,物理内存

时间:2019-10-07 08:59来源:数据库
重启SQL Server服务以使配置生效; 成功开启LPIM后,在SQL Server errorlog里可以看到类似字样:Using lockedpages in the memory manager.;未开启LPIM的话,在SQL Servererrorlog里可以看到类似字样:Using
  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.;

配置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的入口缓存就需要更多去循环利用。因此使用大页面去分配虚拟地址空间在性能上会有一定的提升。

澳门新濠3559 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

澳门新濠3559 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分钟时间来分配页面

澳门新濠3559, 

 

总结:

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

 

 

相关文章

SQL Server and Large Pages Explained….

 

如有不对的地方,欢迎大家拍砖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位版本

澳门新濠3559 3

 

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

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

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

具体参考:

 

 

 

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

 

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

I realize that the topic of “locked pages’ and AWE can be confusing. I don’t blame anyone for being confused on this. I also realize we have blogged and talked about this topic many times perhaps “beating it to death”. And I certainly know this is not really fun to anyone(but it made for a catchy title). But I still get questions both from customers and internally within Microsoft about these topics for both 32bit and 64bit SQL Server systems. So I thought a blog post that summarizes and clarifies a few points might be valuable. Maybe this will be the one resource that “turns on the light” for you. Many people respond well to a “FAQ” so I’ll create this topic in that format. I recommend you read through each FAQ one at time in order, because some of the answers need to be read first to understand the ones below it:

1. What is the difference between AWE on 32bit systems and “Locked Pages” on 64bit systems?

I think it is all about using the right terms. SQL Server introduced a “feature” called AWE before we even shipped a 64bit version of SQL Server. The concept was to extend the ability of SQL Server to address more memory than the limits of the virtual address space (VAS) on 32bit systems (which is 2Gb-3Gb). But the SQL team couldn’t do this alone. So the Windows team built an API to support this capability called Address Windowing Extensions.(AWE) If you want to go a step further and find out what is the difference between Physical Address Extensions (PAE) and AWE, read this resource.

One of the interesting effects for any application that uses the AWE API is that any memory allocated with these APIs is not part of the process working set. Therefore, Windows considered this memory as “locked” (i.e. cannot be paged to disk). Therefore, the user who launches an app that uses the AWE API must have the “Locked Pages in Memory” privilege (for example the service account for SQL Server) set.

So now the SQL team introduces a x64 edition of SQL Server with SQL Server 2005. Because a process running on x64 doesn’t have the same VAS limits as 32bit (the numbers are crazy here. Theoretically, a 64bit process has a 16 Exabyte address limit which Windows doesn’t even support yet. In fact, Windows limits VAS to 8TB but physical memory is limited to 2TB….today), there is no need to use any special APIs to address memory bigger than the VAS. In other words, SQL Server should be free to go back and just use plain ol’ VirtualAlloc() to allocate memory. Well….the developers of the product discovered that if they still use the AWE APIs to allocate memory even though it is not really needed, two things would happen:

  • A small performance gain occurs within the kernel. For more details, read this blog post from Slava Oks:
  • Just as with 32bit systems, any memory allocated using  the AWE API is not part of the working set and therefore cannot be paged to disk. Therefore it is considered “locked”.

Thus was born the concept most refer to as “locked pages” for 64bit SQL Server editions.

Now remember the requirement to use the AWE APIs for any Windows application? The user account running the process must have the “Locked Pages in Memory” privilege set. So in the 64bit SQL Server engine, if this privilege is set (and a SKU check. See a different FAQ below for more discussion on this), we internally use the AWE APIs to allocate memory. By using the AWE APIs, we have in effect enabled a “locked pages” feature for the SQL Server engine.

So in summary the AWE APIs for 32bit and 64bit SQL Server systems are used for different purposes. In 32bit it is really to extend memory access beyond 4Gb or to enable the AWE feature. For 64bit systems, it is to possibly gain performance and to “lock pages” for the buffer pool.

2. Do I need to use the “awe enabled” sp_configure option on 64bit systems for SQL Server to “lock pages”?

No. In fact, the code for SQL Server for 64bit systems ignores this sp_configure option. It is a “no-op” for 64bit SQL Server systems. You may ask why is this the case if I just told you that AWE APIs are used in 64bit SQL Server systems to “lock pages”?

The answer is based on the purpose for that sp_configure option. The purpose of this sp_configure option on 32bit systems is for the user to “enable” the “AWE” feature, which is I explained above is to extend the ability to reference memory > 4Gb. Now as I mentioned already in order to use the AWE APIs you must have the “Locked Pages in Memory” Privilege. So, when you try to use sp_configure to set ‘awe enabled’ on a 32bit we actually will fail this command if “Locked Pages in Memory” is not set.

3. Why is Task Manager not showing all of the memory allocated for SQL Server?

Imagine you walk up to a 64bit SQL Server installation and the customer tells you the computer has 8Gb of physical memory and SQL Server perfmon counters such as “Total Server Memory” show SQL Server is using around 3Gb of that. But you open up Task Manager on this Windows Server 2008 machine and look at the columns for SQLSERVR.EXE in Task Manager and see something like this:

澳门新濠3559 4

You can see in this example, that the column for “Memory” for Task Manager shows only ~135Mb. But on this computer, if I look at Perfmon and show Total Server Memory, I see this:

澳门新濠3559 5

Why the difference? Well, the most likely reason is that this SQL Server 64bit instance is using “locked pages” as I’ve described in an earlier question. Notice the name on the Task Manager column is called Memory (Private Working Set). Remember we also said that if SQL Server 64bit instances use “locked pages” this memory would not be part of the working set (because remember AWE APIs are used on 64bit to “lock” pages and that memory is not part of the working set). So since the locked pages are not part of the working set, they won’t appear in this column in Task Manager. On the Task Manager for Windows Server 2003, this column is called “Mem Usage” but it also reflects the working set of the process.

How can we prove this “difference” in memory is due to locked pages? Well, there are several ways to do this, but one way to easily see this in SQL Server 2008 is to query the sys.dm_os_process_memory DMV. On my computer where I saw this behavior I queried this DMV and saw these results:

澳门新濠3559 6

You can see from this DMV that the column locked_page_allocations_kb is close to the Total Server Memory perfmon counter and shows that this memory is actually “locked”.

4. Now I know that SQL Server on x64 can use “Locked Pages”, what exactly is locked?

The simple, direct answer to this question is any memory allocated through the Buffer Pool Manager for SQL Server. But what is the “Buffer Pool Manager”? Does this mean only “database” pages are locked? Starting in SQL Server 2005, all memory allocations (in other words all access to the Windows API for memory) go through the SQLOS component of the engine. Any code in the SQL engine that need to allocate memory <= 8Kb use something called the “Single Page Allocator” (SPA) in SQLOS. It just so happens that SQLOS redirects any SPA requests to the Buffer Pool Manager. This is the same buffer pool code that has been used since SQL 7.0 to allocate memory. So any memory needed in the engine that wants “single pages” ultimately goes through the Buffer Pool. And… the Buffer Pool is the code that has the logic to lock pages via the AWE APIs.

So…any code using the Single Page Allocator (SPA) which uses the Buffer Pool manager, will have its memory “locked” if the Buffer Pool Manager is using locked pages. What code uses the Single Page Allocator? Aside from the what you may already guess which are database pages, you can find out what “type” of memory uses the SPA by querying the sys.dm_os_memory_clerks DMV. Look for any row where the single_pages_kb column is > 0.  If you run this query on your server you are likely to see clerk types of CACHESTORE_OBJCP and CACHESTORE_SQLCP. This procedure cache memory. I won’t list out all of them here but you can see several “types” of memory use SPA which means they use Buffer Pool which means this memory can be locked.

5. Does the Standard Edition of SQL Server 32bit support AWE? What about “Locked Pages” for 64bit?

I’ve seen some people ask me and others at Microsoft whether the Standard Edition of SQL Server for 32bit supports the “AWE” feature. I think the confusion is related to the fact that Standard Edition for SQL Server for 64bit until recently did not support locked pages.

So let’s dispel this myth here:

  • The Standard Edition of SQL Server for 2005 and 2008 32bit DOES support the AWE feature. One source to confirm this is at . This lists “Dynamic AWE” as a feature support for both Standard and Enterprise Editions. As I wrote this blog I wanted to confirm this by going to the source..literally. I checked our code and our logic to “enable AWE” is based on Enterprise OR Standard Editions.
  • Up until recently the Standard Edition of SQL Server 2005 and 2008 64bit did NOT support the “locked pages” feature as I’ve described it earlier in this blog post. Based on customer feedback we changed this in recent cumulative updates for both 2005 and 2008.  Enabling this for Standard requires a trace flag and you can read more about this at

6. How do I know if AWE is enabled on a 32bit SQL Server? How do I know if “Locked Pages” is working on a 64bit SQL Server?

Read this blog post first which gives details on the algorithm in the engine for enabling AWE or for enabling “Locked Pages” .

For 32bit systems, one tip. If you think you set “awe enabled” to 1 and are not sure why you are not seeing Address Windowing Extensions is enabled in the ERRORLOG, it is likely you either didn’t run RECONFIGURE after changing “awe enabled” to 1 OR the “Lock Pages in Memory” privilege is not set. If you try to reconfigure after changing “awe enabled” to 1 and the “Lock Pages in Memory” privilege is not set, the RECONFIGURE command will fail with:

Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

 

I hope this information has helped you understand the concepts of AWE and “Locked Pages” with respect to SQL Server. I know this can be a confusing topic and I don’t blame anyone for asking question to understand this. If I see comments to this post or run into any common questions that make sense I’ll add this to the FAQ on this blog.

 

Bob Ward
Microsoft

Published Friday, September 11, 2009 8:40 PM by psssql

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

 

 

  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

 

跟踪标记:845

 

注意:

 

 

 

                             | Page Allocator | 

 

 

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

Buffer Pool

 

 

DBCC TRACEON - Trace Flags (Transact-SQL)

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

 

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

 

澳门新濠3559 7

               Fig. 1

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

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.

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

Future posts

 

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

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

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)

  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;

 

 

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

备注:

 

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.

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

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

参考:

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

功能:

Fig. 3

Server Memory Server Configuration Options

 

  1. 打开SQL Server Configuration Manger;

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

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

Fig. 4

 

 

Tuning options for SQL Server when running in high performance workloads

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

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

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.

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

 

 

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.

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

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.

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

 

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

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.

 

                             | Memory Node | 

 

 

Trace Flag 834 and When to Use It

 

                                           /                     |                     

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

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.

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

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

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

 

澳门新濠3559 8

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.

                             | Memory Object | 

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

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

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.

功能:

 

澳门新濠3559 9

 

跟踪标记:834

                               /                /

SQL Server and Large Pages Explained

                                                  /   

Buffer Pool and AWE mechanism

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

 

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.

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

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.

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

 

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!

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

                          /                       /

                             /                   

Memory Clerks

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.

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.

                                               /                 |                 

 

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.

                                         /                      /                     /

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.

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:

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

                                             /                   |                   

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.

Memory objects

 

          | Memory Clerk |                 |     Caches    |

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.

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

 

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

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

Memory Node

 

                           /                       

 

from:

编辑:数据库 本文来源:从而可以提高CPU转换检测缓冲区(TLB,物理内存

关键词: 澳门新濠3559