-Inside SQL Server 2000's Memory Management Facilities

Original article: Inside SQL Server 2000's Memory Management Facilities
Translation: RicCC

Ken Henderson
Microsoft Corporation
January 2004
 
This topic is excerpted from Ken Henderson's "The Guru's Guide to SQL Server Architecture and Internals" (Addison-Wesley, 2003 ). License. Copyright.
Abstract: Ken Henderson explores SQL Server Memory Management from the developer's perspective.

Introduction
In this column, we will discuss SQL Server Memory Management from the developer's perspective. That is to say, we will discuss how SQL Server uses APIs and operating system functions to manage memory and how it works. Exploring a product in this way will help us understand the ideas of product developers and how they design and use them. Understanding the working principle of a product and its intended use are the key to understanding the product.
We will start with the introduction of basic Windows Memory Management Principles. Like all 32-bit Windows applications, SQL Server uses the Windows Memory Management Function to allocate, release, and manage memory resources. It calls the Win32 memory management API function, interacts with memory resources provided by the operating system.
Since almost all memory allocations in SQL Server use virtual memory (not a memory heap), most of the memory allocation code is ultimately completed by calling the VirtualAlloc or VirtualFree function of Win32. SQL Server calls VirtualAlloc to reserve and submit the virtual memory, and CALLS VirtualFree to release the virtual memory.

Virtual Memory and physical memory
On x86 processors, Windows provides a 4 GB virtual memory workspace for all processes. The word "virtual" means that the memory is not the memory in the general sense. It is only an address range and is not associated with a physical storage unit. When a process requests memory allocation, the address space is used and associated with a specific physical storage unit. However, these physical storage units are not necessarily physical memory. They may usually be disk space. Specifically, they are operating System Paging Files ). This is why multiple applications can run on a M memory system at the same time, and each application has a 4 GB virtual memory address space-it is not the real memory, but it can be understood as memory for applications. Windows transparently processes the data exchange of paging files, so that the memory that applications can use can exceed the actual physical memory of the machine, and enable the application to access the physical memory of the machine fairly.
This 4 GB address space is divided into two parts: user mode and kernel mode. By default, the size of each part is 2 GB. in Windows NT operating systems, you can use BOOT. the switch in INI to change this default setting (Windows NT, Windows 2000, Windows XP and Windows Server 2003 belong to Windows NT series, Windows 9x and Windows me do not ).

Figure 1: Windows divides the virtual address space of a process into two parts: user mode (application) and kernel mode (operating system ).
Each application has its own virtual memory address space, but the operating system and the device driver share the same private address space. Each Virtual Memory Page is associated with a specific processor mode. to access a virtual memory page, the processor must work in the required mode. This means that applications cannot directly access the virtual memory in kernel mode. The system must switch to kernel mode to access the memory space in kernel mode.

Application memory Adjustment
The 3 GB startup option (available in Windows 2000's Advanced Server and DataCenter and later Windows versions) allows you to change the default size of the two address spaces. It allows the process to expand the user mode address space from 2 GB to 3 GB, the cost is that the kernel mode address space is reduced from 2 GB to 1 GB. In Windows, this function is called application memory adjustment or 4 GB adjustment (4 Gt ). You can enable application memory adjustment by adding the/3 GB switch in the [Operating Systems] section of the BOOT. ini file. Generally, you can set the [Operating Systems] section of the BOOT. ini file to enable the system to start with 3 GB or not with 3 GB, so that you can choose when the system starts.
Warning you can also use the/3 GB switch on Windows 2000 Professional and Windows 2000 Server. The negative result is that the kernel mode space is reduced to 1 GB, however, it does not increase the user mode space. In other words, you reduce the space in the kernel mode but do not get any benefits.
Note: Windows XP and Windows Server 2003 introduce a new startup option/USERVA, which can be used together with/3 GB for better control than/3 GB. You are in BOOT. when adding/3 GB in INI, you can add/USERVA at the same time. The advantage of/USERVA is that it allows you to specify an accurate address space size value for user mode access. For example,/USERVA = 2560 configures 2g space for the user mode, and the remaining 1g space is used in the kernel mode. The preceding warning information also applies when the/USERVA option is used.

Declares an executable program for large-address access.
Before/3 GB can be added to Windows, the application cannot use the highest bit of the pointer. the user-mode application can only access the address space represented by the first 31 bits of the 32-bit pointer. For the remaining one, some smart developers do not want to waste this one in the process space and use it for other purposes, for example, a pointer is used to identify the specific address allocation type of the application. This brings about a challenge after the introduction of/3 GB, because this type of application cannot distinguish between pointers that reference more than 2 GB memory and those that reference less than 2 GB memory but use the highest bit for other purposes. Basically, starting the machine with/3 GB will cause such an application to crash. To solve this problem, Microsoft adds a new identification space to the Characteristics field in the Win32 PE File Format (defining the formats of executable files Exe and Dll structures in Windows, indicates whether an application supports large addressing capabilities. Set the 32nd-bit IMAGE_FILE_LARGE_ADDRESS_AWARE flag for the Characteristics field in the executable file header. By setting the flag of the application header, it indicates that the application can process the pointers set for the highest bit without any ambiguity caused by this bit. When this flag is set and started with the/3 GB option in the correct Windows version, the system will provide a private Extended user mode address space for the process. You can use DumpBin, ImageCfg, and other tools that can analyze executable file headers to check whether the application has enabled this flag. Visual C ++ supports IMAGE_FILE_LARGE_ADDRESS_AWARE through the/LARGEADDRESSAWARE connection switch. SQL Server enables this flag. Therefore, when you enable the/3 GB switch in the correct Windows version, the system will expand the user mode address space of SQL Server.
Note: Windows checks the IMAGE_FILE_LARGE_ADDRESS_AWARE ID of the executable file when the process starts, ignoring the Dll ID. The dll code must be correctly processed for the pointers with the highest bits.

Physical address extension
Starting with Pentium Pro, the Intel processor provides a memory ing mode called Physical Address Extension-PAE. PAE supports up to 64 GB physical memory access. In PAE mode, the Memory Management Unit-MMU still implements Page Directory Entries (Page Directory Entries-PDEs) and Page Table Entries (Page Table Entries-PTEs ), however, there is a new level above this: Page Directory Pointer Table ). In PAE mode, the system can address a larger memory, because PDEs And PTEs are 64-bit width, which is twice the standard width, rather than the page Directory Index Table in PAE mode. The page Directory Index Table manages these high-storage tables and indexes. In PAE mode, a special version of Windows kernel is required. In Windows and later versions, the single processor machine is located in ntkrnlpa.exe, and the multi-processor machine is located in Ntkrnlpamp. Like/3 GB and/USERVA, add/PAE to the BOOT. ini file to enable the PAE mode.

Address window Extension
The Address window Extensiongs feature in Widnows allows applications to access more than 4 GB of physical memory. A 32-bit pointer is an integer and can only store values smaller than or equal to 0xFFFFFFFF. Therefore, it can only reference A 4 GB linear memory address space. AWE allows applications to break through this restriction and access the memory supported by all operating systems.
In concept, AWE is not a new thing. In fact, starting from the birth of a computer, operating systems and applications began to use similar mechanisms around pointer restrictions. For example, back to the DOS era, 32-bit extensions (such as Phar Lap, Plinks, and others) are widely used in 16-bit applications to access memory outside the normal address space. Special managers and APIs used to expand memory are very common. Maybe you still remember products like Quarterdeck QEMM-386, which were widely used in that era. In these mechanisms that allow the pointer to access memory that exceeds its own expression range, a representative method is to provide a window or area in the address space where the pointer can be directly accessed, it is used to convert memory areas that cannot be directly accessed with pointers. This is exactly how AWE works: it provides an area in the process address space, or a window for the user-mode code to directly access the memory area for memory access and exchange.
To use AWE, the application must:
1. Use the AllocateUserPhisycalPages API function of Win32 to allocate extended physical memory. This function requires the caller to have the permission to lock the Memory Page.
2. Use the VirtualAlloc API function to create an area in the process's address space as a window for ing with extended physical memory.
3. Use the MapUserPhysicalPages or MapUserPhysicalPagesScatter API function to map extended physical memory to this virtual memory window.
Windows 2000 and later versions support AWE. Although AWE can be used on machines with less than 2 GB physical memory, it is generally used only on machines with 2 GB or more memory, because AWE is the only method for 32-bit processes to access memory larger than 3 GB. If AWE is enabled on SQL Server on a system with less than 3 GB physical memory, the system ignores this option and uses the normal virtual memory management method. An interesting feature of AWE memory is that it does not use disks. You will notice that AWE-related API functions only access the physical memory, which means that AWE memory is the physical memory, it will not be exchanged with system paging files.
The Virtual Memory Window used for AWE must have read and write access permissions. Therefore, when you set this virtual window, the protection attribute passed to VirtualAlloc can only be PAGE_READWRITE. This also means that you cannot use VirtualProtect to protect the memory pages in this region to prevent modification or access.
Note: Some commonly used tools used to check the application memory, such as task manager and Perfmon/Sysmon, cannot display the AWE memory usage of each process. There is no tool to indicate the AWE memory usage of each process, that is, there is no tool to report the AWE memory size in the workspace of a given process.

/3 GB and AWE
In Windows Memory Management, application adjustment (/3 GB) can increase the address space of private processes by 50%, which is convenient to use. Therefore, it is a common method, however, AWE is more flexible and scalable. As mentioned above, when you increase the address space of a private process by 1 GB, this 1 GB comes from the address space in kernel mode, and the address space in kernel mode is also compressed from 2 GB to 1 GB. For kernel-mode code, the complete 2 GB workspace is already narrow. compressing this space means that some internal core structures must also be compressed. These structures mainly contain table Windows window for memory management on the machine ). After you compress the kernel mode to 1 GB, the table can only manage up to 16 GB of physical memory. For example, if you run Windows 2000 DataCenter on a machine with 64 GB physical memory and the/3 GB option is used at startup, you can only access 25% of the memory on this machine, the remaining 48BG will not be used by the operating system and applications. AWE allows you to access more than 3 GB of memory, and through/3 GB, you only get an additional 1 GB for private process space. Large Address Aware makes this extra space available to the application automatically and transparently, but it is limited to 1 GB. Theoretically, AWE uses the Win32 awe api function to make all the physical memory available to the operating system available to the application. Although AWE is more difficult to use and access, it is more flexible and scalable.
It doesn't mean that AWE is better than/3 GB in any case, but it is usually the case. For example, if you need a lot of Memory to allocate space, and you cannot store it in AWE Memory (such as Thread stack Stacks, Lock Memory, and Procedure Plans ), you may find that/3 GB is more suitable.

Memory Area
SQL Server organizes the allocated memory into two independent regions: BPool and MemToLeave. In fact, if you use the AWE mode, there is another area: extended physical memory areas 3 GB or more that can be accessed with Windows AWE support.
BPool is a prominent allocation pool in these three regions. It is the primary allocation pool of SQL Server, mainly used for data and index page caching, and for memory allocation smaller than 8 KB. MemToLeave contains the virtual memory that is not used by the BPool in the user mode address space. The AWE memory above 3 GB is used as a BPool extension to provide additional space for data and index page cache.
When you start SQL Server, SQL Server calculates the BPool upper limit based on the physical memory and user mode address space of the machine. After this value is calculated, the MemToLeave region is reserved, which helps prevent subsequent BPool reservation from causing memory fragmentation. Next, we reserve BPool. It can be divided into up to 32 independent reserved blocks to meet the dll and other allocation requests that are requesting virtual address space in the SQL Server process when BPool is reserved. After the BPool region is reserved, The MemToLeave region is released. MemToLeave is used for SQL Server's internal continuous space allocation requests that exceed 8 KB, as well as external customers such as OLEDB Provider and In-process COM Object (outside of the main engine of SQL Server, memory requesters residing in the SQL Server process) Allocate requests.
Therefore, once SQL Server is started, BPool is reserved but not allocated. MemToLeave is basically the idle part of the virtual memory address space of the process. If you view the Virtual Bytes Perfmon counter of the SQL Server process after SQL Server is started, you will find that it reflects the BPool reserved value. I have seen people often panic because of this number. After all, it is usually the total physical memory of the machine or the maximum user mode address space, minus the size of the MemToLeave area. This is nothing to worry about, because it is only reserved but not allocated space. As mentioned before, the reserved space is only an address space, and will not be truly associated with physical storage units until it is allocated. After that, the memory allocated to the BPool will increase until it reaches the BPool upper limit calculated during SQL Server startup.

Monitor SQL Server virtual memory usage
You can track and calculate the maximum BPool value through the SQL Server: Buffer Manager \ Target Pages Perfmon counter. When memory is required for different parts of SQL Server, BPool allocates 8 KB pages from the reserved areas at startup until the upper limit is reached. You can use SQL Server: buffer Manager \ Total Pages Perfmon counters track allocated virtual memory usage in BPool. In addition, you can use the Private Bytes counter to track the usage of all allocated virtual memory in the SQL Server process.
Because most of SQL Server's virtual memory usage comes from BPool, these two counters usually increase or stabilize one after the other (Remember, when AWE support is enabled, private Bytes counters do not reflect all memory usage of SQL Server ). If the Total Pages counter is stable and the Private Bytes continues to increase, this usually indicates continuous memory allocation in the MemToLeave region. This memory allocation may be quite common, for example, it may be the memory allocation related to SQL Server when creating additional working threads, or memory leakage of external requestors such as COM objects in the process and extended storage process. If the MemToLeave region is exhausted due to memory leakage or excessive memory usage, the SQL Server process has used up the memory of the virtual memory address space (or the default process stack size of the maximum idle block in the MemToLeave region is less than MB ), even if the maximum number of working processes configured with sp_configure is not reached, SQL Server cannot create a new working process. In this case, if SQL Server needs to create a new working process to execute the request, such as processing the new connection request of SQL Server, the request will be postponed, wait until the server has enough resources to create a working process, or other working processes are released. This may cause the user to be unable to connect to the server, because the connection may time out before obtaining sufficient free space from MemToLeave or other worker processes release enough resources.

Allocate
When initializing a memory request, the memory requestor in SQL Server creates a memory object to manage the current request. When the memory object executes the request, it calls the corresponding Memory Manager in SQL Server to obtain the memory from the BPool or MemToLeave region. When the request is smaller than 8 KB, the memory is usually obtained from the BPool. when the request is 8 kb or a larger continuous space, it is usually obtained from the MemToLeave region. Because a memory object may generate multiple allocation requests, it is possible to allocate requests smaller than 8 KB from the MemToLeave region. Memory requests to SQL Server process space are generally internal requesters, that is, the internal objects of SQL Server need memory to execute a task. Of course, this is not absolute, as mentioned above, it may also be an external requestor. Generally, these external requestors use Win32 memory API functions to allocate and manage memory, so they are allocated from the MemToLeave region because (for the operating system, note) only the MemToLeave area is available in the SQL Server process. (The BPool area is reserved by SQL Server ). However, the extended stored procedure is a special case. The extended stored procedure calls the srv_alloc API function implementation of ODS, which makes it the same as the internal requesters of SQL Server, generally, memory smaller than 8 KB of srv_alloc requests are allocated from the BPool, and large memory allocation comes from the MemToLeave region.

Memory Manager
When the server is running, the Memory Manager checks to ensure that a certain amount of available physical memory is reserved for the server, so that other applications on Windows and the server can continue to run smoothly. This number ranges from 4 MB to 10 MB (Windows Server 2003 is close to 10 MB) based on the system load and the Memory Page life cycle in BPool. If the available physical memory on the Server starts to fall below this limit, the Server releases some memory pages in BPool to reduce the memory usage of BPool (assuming that the dynamic memory configuration of SQL Server is enabled ). The Memory Manager also ensures that a certain number of idle memory pages are retained at any time, so that when new allocation requests arrive, there is no need to wait for memory allocation. The idle memory indicates that these memory pages are allocated but not used. The allocated but not used BPool Memory Page is tracked through an idle list. When the page in the list is used, the memory manager allocates more memory pages from the BPool reservation, until the entire BPool reservation is allocated. You will see that the Process: Private Bytes Perfmon counter is gradually increasing due to this behavior (usually linear growth ).
Each CPU in the system has a separate idle list. When you need to use the idle page to satisfy a allocation request, first check the idle list related to the CPU of the current allocation request, check other CPU-related lists in the system. In a multi-processor system, this helps each processor to better use the local cache and improve scalability. You can use the SQL Server: Buffer Partition Perfmon counter to monitor specific BPool partitions and use the SQL Server: Buffer Manager \ Free Pages Perfmon counter to monitor the idle list of all partitions.
During the entire running process, the SQL Server Memory Manager Process (may run in the memory manager thread or other Server threads) monitors the system memory status and reserves a reasonable amount of idle physical memory for other applications in the system, reserve a Safe Memory Page for the new memory allocation request. When AWE is used on the server, some of these aspects must be changed. When AWE is used, BPool obtains and locks the physical memory of the machine from the beginning. The number of locked memory is determined based on whether maximum server memory is set. If this parameter is set, BPool tries to lock the number determined by maximum server memory. If this parameter is not set, BPool only sets about 128 MB for use by other processes and locks all the remaining physical memory on the machine. Then, BPool uses the memory above 3 GB (AWE memory) as the paging files for data and indexing. It splits these areas (above 3 GB) the physical memory page is mapped to the appropriate virtual memory address space, so that the 32-bit pointer can be referenced.

Review
This part is not translated...
 
Ken Henderson, a husband and father, lives in the Dallas suburb of Texas. He is The author of eight books on different technical topics, including The recently released The Guru's Guide to SQL Server Architecture and Internals. Ken Henderson is a fan of the Dallas Mavericks. He enjoys watching their children and sports and gardening in his spare time.