In-depth SQL Server 2000 memory management mechanism (3)
Http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
Memory area
SQL Server is divided into 2 blocks to organize memory allocation, which is BPool (Buffer Pool) and MemToleave (memory release area), and if you use AWE memory, then there is actually a third zone: Windows AWE supports higher than 3GB Physical memory area.
The buffer pool area is the best in this 3 memory area, which is the buffer pool initially allocated by SQL Server for the original data page and index page use, and is used to assign memory for less than 8K. MemToleave is a virtual memory space consisting of memory spaces that are not used in the user memory space. Windows AWE Calls 3GB of Memory Spaces as a buffer pool area to provide additional memory space cache data pages and index pages.
When you start SQL Server, the upper limit of the buffer pool area is based on the physical memory estimate or the size of the user memory space. Once the size of the buffer pool area is determined, the memory release area is followed, and the partially divided fragments are retained by the rear buffer pool region. The buffer pool area is then next to the memory release area, running the DLL file using 32 individual reserved regions and other virtual memory spaces used in SQL Server when the buffer pool area is predetermined. When the buffer pool area is reserved, the memory release area is released. This area is used inside the SQL Server to extend the 8K data page and assigned to other external applications (like: memory consumers are SQL Server processes other than the primary engine of SQL Server), such as Ole DB Providers, COM objects, and more.
Therefore, when SQL Server has been started, BPOOL (buffer pool zone) is reserved, but does not commit, while the MemToleave (memory release area) in the virtual memory space is actually free. If you pass the performance analyzer The Virtual Bytes Perform counter, after SQL Server launches the process of SQL Server, you will see that it can reflect BPOOL (buffer pool area) reserved area. I see people some panic, because this value is relatively high - after all, it The total physical memory reflecting this machine is the largest user memory space minus MemToleave (memory release area). This is not worried, compare this is just a reserved area, there is no submitted space. Just as mentioned earlier, The retention space is just the address space - there is no real physical memory storage until the memory space is submitted. Over time, the memory space is submitted, the BPOOL (buffer pool area) will increase, know the upper limit of the last determined when the Server is initiated.
Monitor the use of SQL Server virtual memory
You can track the biggest space determined by the SQL Server: Buffer Manager / Target Pages Perform counter. Because the different parts of Server requires memory, BPOOL (Buffer pool area) submitted 8K size page (this is the original reserved) Until the sizes submitted to determine the target). You can track the submitted virtual memory using the SQL Server: Buffer Manager / Total Pages Perform counter, you can track all SQL Server processes used through the Pivate Bytes counter. Virtual Memory.
Because most of the virtual memory of SQL Server is from BPOOL (buffer pool area), you can know through the above 2 counter. In general, growth and smooth are one-lasting place. (Please keep in mind: When the application Startup support for AWE, the Pivate Bytes counter does not reflect the overall SQL Server memory usage). If the Total Pages Perform counter is horizontal and the Pivate Bytes counter is tilted up, this is generally distributed from the MemToleave (Memory Release Zone) Memory. This assignment process will end normally - for example: assigning the associated thread stack in Server as an additional work thread, which may also be an internal measurement COM object or XProc's memory leak. If a program is because MemToleave (memory release Zone) exhausted and running out virtual memory space due to memory leakage or memory excessive consumption. (Or biggest idle block in MemToleave) to the default thread stack 0.5MB below the default thread stack, so SERVER can not generate new The working thread, even if the value of SP_Configure Max Worker Threads is not. In this case, if Server needs to generate a new working thread to perform a work request, such as processing a new connection request to Server, these work will Will be delayed until Server can generate new threads or other threads. This way, the system will prevent one user from connecting to Server before having enough MemToleave release or other working threads can be effectively processed. Because this connection will time out. The memory distributor initializes a memory distributor in a memory consumer in Server, first producing a memory object to manage these requests. When this object is assigned these requests, he is In the Server Memory Manager, these requests are fulfilled from the BPool (Buffer Pool Region) or MemToleave (Memory Release Zone). If these requests are less than 8K, these requests are usually assigned in BPOOL. If requests need 8K or more Memory space, which is usually assigned in MemToleave. Since a separate memory object can be used to perform multiple memory allocations. So there is a request for a memory allocation just below 8K (including administrative objects) Assigned in MemToleave (memory release area). In SQL Server's processing space, memory consumers are usually internal. In other words, these memory consumers and objects are SQL Server's own specifications that need to consume memory to perform tasks, but Not necessarily this. There is also one Some external consumers are like the front locks. Typically, these external memory consumers call the normal Win32 API memory function to assign and manage memory, and allocate memory space from MemToleave, which is very obvious It is the only valid area in the SQL Server program. However, XPROCs have special exception handling, which is the same as the Open Data Services (ODS) SRV_alloc API function, which is the same as other memory consumers. Generally speaking, SRV_alloc API functions from BPOOL (Buffer Pool Area) Application is less than 8K memory, and allocates from MemToleave (Memory Release) for large memory.
Memory manager
When Server is running, memory managers check the remaining valid capacity of physical memory to ensure that Windows and other applications can run smoothly. This valid memory is directly changed in 4MB and 10MB. (Closer to 10MB in Windows 2003). ) And this page lifecycle based on system kernel loading and BPOOL (buffer pool zone). If the effective physical memory space on Server is below this threshold, Server will reduce the submission of BPOOL (Buffer pool area) page to shrink memory physical storage Use (assuming dynamic memory allocation is activated). Memory managers also guarantee that the submitted memory page is idle after the specified time point, which is no longer waiting for memory allocation. "I mean, I mean: After the memory page is submitted, but not used. Submit BPOOL (Buffer Pool District) page can be tracked through an idle page list. Things all the new page comes from the idle page list, Memory managers submit more page from BPool (Buffer Pool) Reserved Area to all reserved districts. You see: Process: private Bytes Perfmon counter slow growth (usually a straight line) is this reason. In the multi-CPU system, no one has a separate idle page list, when an idle page is required to respond to a request request, first meet the assigned space in the current CPU's idle page list, then other CPUs The idle page list. This better uses the local cache of each processor, improves the stability of the multiprocessor's Server. You can monitor the specified BPOOL via SQL Server: Buffer Partition Perform objects (buffer pool area) Zone, you can monitor all BPOOL (buffer pool area) area via SQL Server: Buffer Manager / Free Pages Perform counter.
Therefore, during the entire running process of SQL Server, SQL Server's memory processor (regardless of memory thread manager or other thread service) monitors the system's memory usage, confirming the number of reasonable remaining idle physical memory to other systems and pre- Remaining a reasonable idle page response to a new memory request. When Server uses AWE memory, some memory status must be changed. BPOOL (Buffer Pool District) starts through the physical memory of Server, and the number of memory locks is based on the basis The server's maximum memory is set. If configured, the BPOOL (Buffer Pool Region) area tries to lock the corresponding number of memory according to the largest server. If no configuration, the BPOOL (buffer pool area) district locks close to all 128MB of memory. Physical memory, only a small amount of area is left to other processing. Then BPOOL uses the physical memory (AWE memory) higher than 3G (AWE memory) as a page file that runs data and index. BPOOL (Buffer Pool District) Mapping physical memory and virtual memory, can therefore be referenced by a 32-bit pointer.
Overview
You already know: SQL Server Memory Manager is a difficult point. Understanding how a program assigns and manages memory is the basis for how the program works. Memory is a very important resource, its effective use is a reliable application design Basic factors, knowing that an application memory management mechanism will be your program design such as tiger.
A developer, how did he affect you? Understanding the Server's memory management mechanism gives you how to write an efficient application and solve the insight of some and memory related issues. For example, debugging the client connection speed In the middle, you improve the size of the default network package of SQL Server is 8K., Immediately, SQL Server immediately start writing error messages in Error log, prompting the virtual memory in the MemToleave area there is a problem. See like this After the information, you will know this change at least a part of the problem, because you know that the memory is allocated 8K or more memory is in the MemToleave area. This SQL Server connection associated cache is also from this area, because you The size of the network package has been configured. Configuring the default network package is suitable for the .NET Framework's SQLCLINET Provider 8kb. This situation is not so reasonable. In fact, this is a very common problem, due to MemToleave The release area is caused by lack of memory space, because the network package is too large, at least some parts are like this.
At the same time, you can help you understand whether the code you customize in SQL Server is in the critical value of system resources, such as data cache. For example, you create an extended stored procedure to call SRV_PRO () functions to assign Memory. Suppose, the cache assigned in your code is less than 8KB. According to our previous discussion, we know that the extended stored procedure is allocated from the BPOOL (buffer pool area). Yes as a data cache. Learn how SQL Server is managed Memory, you can help us arrange the system's start order. This can help you calculate the number of physical memory you want and how to assign and partition to SQL Server. For example: Good understanding of AWE memory allocation and application memory adjustment The relative advantages and disadvantages can help determine if your server needs more than 3GB of physical memory and whether it needs to be added / 3GB at startup.
Configuring and configuring SQL Server's memory management mechanism has a primary conflict in ideas and configured applications running on SQL Server. Understanding how Server can help you design, establish and test SQL Server-based applications.
