How to configure SQL Server to use the Windows system large-page allocations

Today, an implementation of a controllable control enabled me to write this article, and this controllable control is allowed to run on the SQLSERVER2005 Enterprise Edition on the Windows200864 bit server

Using the trace flag 834,sqlserver uses large-page allocations to build an in-memory buffer Pool.

Here is my understanding of large-page allocations and this large-page allocations performance improvement

Page assignments

The virtual address space (Virtualaddressspace,vas) consists of a number of memory pages. These pages have two sizes, small pages with only 4KB size (8KB on the IA64 bit system)

The large page has a 2MB size on a 64-bit system (16MB on the IA64 bit system).

The virtual address space (Virtualaddressspace,vas) is maintained by the structure of the page descriptor , so each virtual address space has a corresponding page table entry in the page descriptor list

(personal sense is similar to system service descriptor SSDT, kernel descriptor Gdt/ldt). The hardware uses this table to translate these portals into a readable format, whenever a virtual address is translated,

Must go to this table to find the entrance to the virtual address. To speed up the process of locating these portals on the table, the CPU maintains a cache called: Translation look-aside buffer (TLB)

A TLB works like the execution plan cache for SQL Server, as long as one portal is translated and the next time there is no need to translate again.

Knowing this, it's easy to understand the difference in performance between big and small pages. When a process has a virtual address space (Virtualaddressspace,vas) (each process has only one virtual address space,

The virtual address space = The memory used by a process is composed of small memory pages, so the entry in the page Descriptor table needs to be increased accordingly, and the number of entries for the TLB cache will increase accordingly.

For example, there are several virtual address spaces that need to be cached, and using small pages (a page 4KB) I need to cache more entries in the TLB. More entry means that whenever you receive a

A new translation request, the TLB entry cache needs to be more recycled. Therefore, the use of large pages to allocate virtual address space in performance will have a certain degree of improvement.

Large page 2MB size, small page 4KB size, such as a process needs 8MB memory, then need 4 large pages or 2000 small pages

So using a small page to allocate the virtual address space, you need to cache more entries in the TLB, but the TLB cache size is limited!!

My personal understanding of the page description chart: