On SQL Server Management of memory [graphic]_mssql

Understanding SQL Server Management of memory is fundamental to SQL Server problem handling and performance tuning, and this article describes the memory principles of SQL Server for memory management.

Second-level storage ( Secondary Storage)

For computers, the storage system is layered. The closer you are to the CPU, the happier it is, but the smaller the capacity (as shown in Figure 1). For example: the traditional computer storage architecture from the CPU from near to far is: CPU in the register, first-level cache, level two cache, memory, hard disk. But at the same time the farther away from the CPU the storage system will be larger than the previous storage system of an order of magnitude. For example, a hard disk is usually a magnitude larger than the memory of the same age.

Fig. 1. Computer Storage System

So for SQL Server, the memory that a normal production system configures is usually not able to load all the data, so it involves level two storage, which is the disk. As the last mechanical storage part of the modern computer system, the disk reading the data requires moving the head (specifically on the disk principle, you can see an article I wrote earlier), and because the data accessed by the database is often randomly distributed in various locations of the disk, so if the frequent read disk requires frequent moving heads, This performance will be very much below.

The computer-Body storage architecture is aware that the computer needs to read memory first for all data on all hard drives, so using a buffer of memory and reducing access to disk IO will be key to improving SQL Server performance, which is one of the starting points of this article writing.

SQL Server engine, a self tuning engine

Because SQL Server used to be geared to the small and medium enterprise market, the SQL Server storage engine was designed to be a product that did not require too much configuration to reduce deployment costs, but this is also a lot of people have been criticized by Microsoft's open configuration too little. As for how SQL Server uses memory, there is little room for direct configuration, only open configurations use AWE, and the maximum or minimum memory that an instance occupies, as shown in Figure 2.

Figure 2. Options for SQL Server controllable memory control

For a specific SQL Server, how to use memory, such as how much is allocated to the execution plan cache, how much is allocated to the data buffer, which cannot be regulated by configuration. This is why many other technology developers have a superiority over developers who use Microsoft technology, and it seems to me that although SQL Server offers few places for controllable configuration, many places can be "indirectly" configured with a knowledge of the principles. This also requires understanding some of the principles of Windows.

How SQL Server uses memory

The SQL Server storage engine itself is a process under Windows, so SQL Server uses memory as well as other Windows processes to request memory from Windows. After Windows is applied to memory, SQL Server can be roughly divided into two parts: buffer pool memory (data pages and free pages), non buffered memory (threads, DLLs, linked servers, and so on). Buffer pool memory occupies most of the memory usage of SQL Server. The buffer pool accounts for memory that is set by the maximum minimum memory in Figure 2, so the memory of the Sqlservr.exe may be greater than the maximum memory set in Figure 2.

Another point is that SQL Server uses memory for how much it uses and does not release it later (unless you receive a notification of Windows memory pressure). My company's development server, for example, looks at memory usage when there is little load, as shown in Figure 3.

Figure 3. Memory usage for SQL Server processes

You can see that when the CPU is 0 loaded, the memory occupies 13 g. This is actually the previous use of SQL Server to Windows requested memory has not been released.

How much memory a specific SQL Server can use is determined by the following factors:

1. Size of physical memory

2. Restrictions on memory for installed versions of Windows (for example, the Windows Server 2008 Standard Edition limits maximum memory to 32GB only)

3.SQL Server is 32-bit or 64-bit

4. Configure SQL Server for memory usage as shown in Figure 2

5.SQL Server version (such as the Express version can only use 1G memory)

Three-tier memory allocation for SQL Server OS

SQL Server OS has three tiers of memory allocation, and dependencies are shown in Figure 4.

Figure 4. SQL Server OS memory dependencies

Memory Node

First and foremost, the role of Memory Node,memory Node is to allow allocation of memory to be transferred from Windows to SQL Server OS level execution. Each instance of SQL Server typically has only one Memory node,memory Node depends only on the hardware configuration of the NUMA architecture. We can see some information about memory node through DBCC Memorystatus, as shown in Figure 5.

Figure 5. View Memory node information

We can see that according to the application memory size classification, can be divided into two parts

1. The application is less than or equal to 8KB of memory, which is used for caching. (Singlepage allocator in Fig. 5)

2. Apply for memory larger than 8KB as a unit, which is called multi-page (or MemToLeave) (MultiPage allocator in Figure 5)

The reason why MemToLeave is called MemToLeave is that SQL Server, although most of its memory is used for buffers, requires some contiguous memory for operations such as SQL clr,linked server,backup buffer, 32-bit SQL Server retains a portion of successive virtual addresses (VAS) for MultiPage allocator when the instance is started. The number of specific reservations can be calculated using the following formula:

Reserved address = (CPU kernel quantity-4) +256 *0.5mb+256mb, usually around 384MB.

Memory Clerk

Let's take a look at Memory clerk,memory Clerk is used to allocate memory to classify the allocate out of memory, which can be simply done as shown in Figure 6.

Figure 6. Classification according to the categories of memory clerk

Note: As you can see from Figure 4, Memory Clerk is just part of allocating memory, and the other part is data caching (buffer Pool)

Buffer Pool

Before you start talking about buffer pool, let's talk about virtual memory.

In Windows, each process has a virtual memory (fictitious address space VAS), 32-bit system is 2 of 32 times, that is 4G, this 4G is divided into two parts of Windows, part of Windows use, the other part is the application use. Virtual memory is not actual physical memory, but for the physical memory mapping, when the physical memory does not exist when the virtual memory pointing to the content, resulting in a page break, to replace a portion of the pages of memory, and then the need to read from the hard disk to memory, about this piece, you can read my previous article: Talking about the management of memory in operating system.

Buffer pool, therefore, buffers the data pages, making it possible for future data to be read to reduce access to the disk.

This part of the buffer pool is the space used in Figure 2 to set the maximum minimum server memory. This minimum value does not mean that SQL Server can consume so much memory when it is started, but the use of SQL Server Buffer pool will not be released once it has exceeded this value.

In the DBCC MEMORYSTATUS, there is a part where we can see the information of the buffer pool, as shown in Figure 7.

Figure 7. Information about the Buffer pool

When the SQL Server instance starts, the VAS address space reserved by the Buffer pool depends on several factors: the actual physical memory and the SQL Server is 32-bit or 64-bit (the limit of 32 bits is 4G, You also want to draw half to windows and subtract MemToLeave space), and for the physical memory used by SQL Server in fact, you can view it by using the following statement, as shown in Figure 8.

Figure 8. View the physical memory used by the buffer pool

The Buffer pool will continue to submit memory requests as needed. Buffer pool If necessary, the buffer pool will continue to consume memory until Windows notifies SQL Server that the memory is too low that the buffer pool can release memory, or the buffer pool occupies the memory and is not freed.

It is also worth noting that the page allocated by the Buffer pool is consistent with the size of the SQL Server OS page, which is 8192 bytes and can only be borrowed in 8k when other parts of SQL Server need to borrow memory from the buffer pool. And this part of the existence of physical memory is discontinuous, which sounds like a buffer pool memory management system , you can understand so, because the buffer pool does not use any SQL Server page allocator, and directly using the interface of virtual or awe Sqlos ' s.

So the memory used by SQL Server can be roughly estimated with this formula: the memory occupied by the buffer pool + memory +multipageallocator allocated from the buffer pool page is not buffer pool memory, as shown in Figure 9.

Figure 9. You can approximate the estimated memory of SQL Server

Memory Object

The Menory object is essentially a heap that is allocated by page allocator and can be viewed by sys.dm_os_memory_objects this DMV, which can see a column of Page_ The allocator_address column, which is the identity of the memory clerk, indicates which memory clerk the memory object is assigned.

Memory bottlenecks in 32-bit SQL Server

As can be seen from some of the basic principles described earlier in this article, because 32-bit SQL Server uses VAS for address assignment, the addressing space is limited to 4GB, and half of this 4GB is allocated to Windows, allowing the buffer pool to use up to 2G of memory, This makes 32-bit SQL Server unusable even if it has extra physical memory.

One solution is to make the memory that SQL Server can use become 3g by reducing the 2G to 1G that Windows uses by default. This can be done by typing bcdedit/set on the command line in Windows Server 2008, setting the Increaseuserva option to 3072MB, and for Windows Server 2003来, you need to add/ 3GB startup parameters.

Another option is to use AWE ( address Window Extension) to allocate memory. AWE adds 4 bits to the 32-bit CPU addressing range to 2 of 36 physical, or 64GB, through the physical Address extension of the computer (the Extension PAE). Basically solves the problem that the addressing range is not enough.

VirtualAlloc and Allocateuserphysicalpages

VirtualAlloc and Allocateuserphysicalpages are the methods that SQL Server uses to request memory from Windows. By default, all of the memory required by SQL Server uses VirtualAlloc to Windows to request memory, which is the operating system level, which is directly corresponding to virtual memory. This leads to a problem where all the memory allocated through VirtualAlloc can be replaced in virtual memory when Windows is under memory pressure. This can cause IO occupancy issues.

The memory requested by the Allocateuserphysicalpages is matched directly to the lower page table, so the memory that is applied using this method is not replaced with memory. In the case of 32-bit SQL Server, the data cache portion of the buffer pool will use this function by opening awe, while the MemToLeave part and another part of the buffer pool (mainly the execution plan cache) still pass VirtualAlloc for memory allocation.

As a result, SQL Server first needs the appropriate permissions before opening the memory allocated through AWE, otherwise the error will be in the log, as shown in Figure 10.

Figure 10. Open awe without opening the corresponding permission error

We can set up the account that started SQL Server in Group Policy to have this permission, as shown in Figure 11.

Figure 11. Locked memory page (lock page in Memory)

Problem with 64-bit SQL Server

64-bit Windows basically does not already have this memory problem, but still note that by default, 64-bit SQL Server is still using VirtualAlloc for memory allocation, which means that all allocated memory will replace the page when Windows is under pressure , this is likely to cause jitter (Buffer pool churn), where pages in SQL Server Buffer pool are constantly swapped into hard drives, resulting in a large amount of IO occupancy (available via Sys.dm_exec_query_memory_ Grants the DMV to view queries waiting for memory, so 64-bit SQL Server can avoid this problem by allocating the date page in the buffer pool through allocateuserphysicalpages for memory allocation. Unlike 32-bit SQL Server, 64-bit SQL Server does not need to turn on awe, just open the Lock Page in Memory as shown in Figure 11.

But this is another problem, because SQL Server locks up the memory page, and when Windows memory is down, SQL Server cannot respond to Windows ' memory emergency (of course, the non-data in the buffer pool The cache and MemToLeave portions are still available, but often not enough, because this part of memory is less consumed than data cache, because SQL Server is characterized by how much memory is used, Therefore, it is most likely that Windows is unstable or even crashes when it is unable to respond to Windows ' low memory. So after "Lock Page in Memory" is turned on, to limit the memory usage of SQL Server Buffer pool, as already mentioned in Figure 2, this is no longer discussed here.

Another problem is that when the buffer pool allocates memory through Allocateuserphysicalpages, the memory that we see in Task Manager Sqlservr.exe only contains data from the buffer pool The cache section and the MemToLeave section, and not the data cache portion, so it seems likely that the sqlservr.exe consumes only hundreds of megabytes of memory and the use of memory is dozens of G. Then we need to look at the SQL Server:memory manager\total Server Memory counter in Perfmon.exe to find the memory that SQL Server is really occupied with.

Summarize

This article describes the rationale for SQL Server for memory management and the portion of SQL Server that is part of memory usage, and for SQL Server performance Tuning, understanding the use of memory is a critical part, and many IO problems can be caused by memory.

Click here to download the PDF version of this article