Talking about the management of SQL Server for memory

Original address: https://www.cnblogs.com/CareySon/archive/2012/08/16/HowSQLServerManageMemory.html

Introduction

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.

Level Two storage ( secondary storage)

For a computer, the storage system is hierarchical. The closer the CPU is, the happier it is, but the smaller the capacity (1). For example: the traditional computer storage architecture from the CPU from near to far in order: registers in the CPU, cache, level two cache, memory, hard disk. But the farther away from the CPU the storage system will be an order of magnitude larger than the previous storage system. Hard drives, for example, are usually an order of magnitude larger than the memory of the same age.

Figure 1: Computer storage System

Therefore, for SQL Server, the memory configured by the normal production system typically does not load all the data, so it involves level two storage, which is the disk. Disk as the last mechanical storage part of modern computer system, read the data need to move the head (specific about the principle of the disk, you can see I wrote earlier article), and because the data accessed by the database is often randomly distributed in various locations of the disk, so if frequently read the disk requires frequent movement of the head, This performance will be very bottom.

The computer-body storage architecture knows that the computer's operations on all data in the hard drive need to be read first to memory, so using a good memory buffer to reduce disk IO Access will be key to improving SQL Server performance, which is one of the starting points of this article's writing.

SQL Server engine, a self-tuning engine

Because SQL Server has historically been oriented to the SMB market, the SQL Server storage engine has been designed to be a product that does not require too many configurations to use, reducing deployment costs, but this is too little for many people who have been criticized by Microsoft for its open configuration. As for how SQL Server uses memory, there is little room for direct configuration, only the open configuration is only using AWE, and the maximum or minimum memory consumed by the instance is shown in 2.

Figure 2. SQL Server controllable options for controlling memory

This is not regulated by configuration for how SQL Server uses memory, such as how much it allocates to the execution plan cache, and how much is allocated to the data buffer. This is the reason why a lot of other technology developers are so superior to developers who use Microsoft technology, and in my opinion, although there are few places where SQL Server can provide controlled configuration, there are many places where it is possible to "indirectly" configure the principle. This also needs to understand 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 you request from Windows to memory, SQL Server uses memory roughly into two parts: buffer pool memory (data pages and free pages), non-buffered memory (threads, DLLs, linked servers, etc.). Buffer pool memory occupies most of the memory usage of SQL Server. The buffer pool occupies memory, which is set by the largest minimum memory in Figure 2, so the memory of Sqlservr.exe may be larger than the maximum memory set in Figure 2.

Also, the memory used by SQL Server is characterized by how much is used and not released later (unless you receive notification of Windows memory pressure). My company's development server, for example, looks at memory usage when there is little load, 3.

Figure 3. Memory usage for SQL Server processes

You can see that when the CPU is 0 load, the memory occupies 13 g. This is actually the memory that was previously used by SQL Server to request to Windows 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. Limitations on memory for installed versions of Windows (for example, Windows Server 2008 Standard Edition limits maximum memory use only 32GB)

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

4.2 Configuring SQL Server for Memory usage

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

three-tier memory allocation for SQL Server OS

The allocation of memory to the SQL Server OS is divided into three tiers, as shown in dependency 4.

Figure 4. SQL Server OS memory dependencies

Memory Node

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

Figure 5. Viewing memory node information

As we can see, according to the size of the request memory classification, can be divided into two parts

1. Apply a memory that is less than or equal to 8KB for one unit, and these memory is used for caching. (Singlepage Allocator in Figure 5)

2. Request memory greater than 8KB for one unit, these memory 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 the 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 subset of consecutive virtual addresses (VAS) for MultiPage Allocator when it launches an instance. The exact number of reservations can be calculated using the following formula:

Reserved address = ((CPU core number-4) +256) *0.5MB+256MB, usually around 384MB.

Memory Clerk

Let's see memory clerk,memory Clerk is used to allocate memory, which is used to classify the allocate out, which can be done simply as shown in the following statement, 6.

Figure 6: Classification According to the memory clerk category

Note: As can be seen from Figure 4, memory clerk is only part of the allocation, and the other part is the data cache (buffer Pool)

Buffer Pool

Before you begin to tell the buffer pool, you first want to talk about virtual memory.

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

The role of buffer pool is therefore to cushion the data page, making it possible to reduce access to the disk in the future when reading data.

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

In DBCC MEMORYSTATUS we can see the information in buffer pool, as shown in 7.

Figure 7. Information about the Buffer pool

When an instance of SQL Server is started, the VAS address space reserved by Buffer pool depends on several factors: both actual physical memory and SQL Server are 32-bit or 64-bit (this limit of 32 bits is 4G, You also want to cut half to windows and subtract MemToLeave space), and for the physical memory that SQL Server actually uses, you can see it in 8, as shown in the following statement.

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

Buffer Pool will continue to make memory requests as needed. Buffer pool if required, buffer pool consumes memory continuously until Windows notifies SQL Server that memory is too low for buffer pool to free memory, otherwise buffer pool occupies memory and will not be freed.

It is also worth noting that the page allocated by 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 the rest of SQL Server needs to borrow memory from the buffer pool. And this part of the physical memory is not contiguous, this sounds like a buffer pool memory management self-system, it can be understood, because buffer pool does not use any SQL Server page allocator, and directly using virtual or AWE Sqlos ' s interface.

Therefore, the memory occupied by SQL Server can be roughly estimated using this formula: Buffer pool occupies memory + memory +multipageallocator allocated from buffer pool of the paged pool memory, as shown in 9.

Figure 9: Approximate estimation of the memory occupied by SQL Server

Memory Object

Menory object is essentially a heap, assigned 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 ID of the memory clerk, indicates which memory clerk is allocated by the memory object.

memory bottleneck for 32-bit SQL Server

As can be seen from the basic principles described earlier in this article, because 32-bit SQL Server uses VAS for address assignment, so the addressing space is limited to 4GB, this 4GB is half to windows, so that buffer pool can only use 2G of memory, This makes the 32-bit SQL Server unusable even if there is 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 consumes by default. This can be done by typing the bcdedit/set settings increaseuserva option on the command line in Windows Server 2008, setting the value to 3072MB, and for Windows Server 2003来 said that you need to add/ 3GB startup parameters.

Another option is to use AWE (Address Window Extension) to allocate memory. AWE is increased by 4 bits through the physical Address extension of the computer (physical address Extension PAE), which increases the 32-bit CPU addressing range to 2 36, or 64GB. It basically solves the problem of insufficient addressing range.

VirtualAlloc and Allocateuserphysicalpages

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

The memory requested by Allocateuserphysicalpages is matched directly to the lower-level page table, so the memory requested using this method is not replaced by memory. In the case of 32-bit SQL Server, the data cache section in buffer pool will use this function by turning on AWE to allocate memory, while the MemToLeave part and the other part of the buffer pool (primarily the execution plan cache) still pass VirtualAlloc for memory allocation.

Therefore, before turning on memory allocation through AWE, SQL Server needs the corresponding permissions first, otherwise it will error in the log, as shown in 10.

Figure 10: Open awe without opening the corresponding permission error

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

Figure 11: Lock page in memory

problems with 64-bit SQL Server

64-bit Windows basically no longer has the above-mentioned memory problems, but still note that by default, 64-bit SQL Server is still using VirtualAlloc for memory allocation, This means that all allocated memory will displace the page when Windows is under pressure, which is likely to cause jitter (Buffer pool churn), which is where pages in the buffer pool of SQL Server are constantly being swapped into the hard disk, causing a lot of Io consumption ( Queries that wait for memory can be viewed by sys.dm_exec_query_memory_grants this DMV, so 64-bit SQL Server will have a date in the buffer pool Page memory allocation through Allocateuserphysicalpages can avoid this problem. Unlike 32-bit SQL Server, 64-bit SQL Server does not need to turn on awe, just turn on the "Lock page in Memory" shown in 11.

But this is another problem, because SQL Server locks up memory pages, and when Windows is out of memory, SQL Server cannot respond to Windows memory emergencies (of course, non-data in buffer pool The cache and MemToLeave sections are still available, but often not enough because this portion of memory consumes less than the data cache, because SQL Server features how much memory is used, Therefore, it is possible to cause Windows to be unstable or even crash when you cannot make a response to Windows low memory. Therefore, to limit the memory usage of the SQL Server Buffer pool after the "Lock page in Memory" has been turned on, as already mentioned in Figure 2 above, this is no longer a detail.

Another problem is that when buffer pool allocates memory through Allocateuserphysicalpages, the memory that we see in the Task Manager contains only the non-data in buffer pool Sqlservr.exe The cache section and the MemToLeave section, and not the data cache section, may appear to cause Sqlservr.exe to occupy only hundreds of megabytes of memory while memory usage is dozens of G. At this point we need to look at the SQL Server:memory manager\total Server memory counter in Perfmon.exe to find the memory that SQL Server actually occupies.

Summary

This article describes the fundamentals of SQL Server for memory management and the parts that SQL Server uses for memory, and understanding the use of memory is a critical part of SQL Server performance tuning, and many IO problems can be caused by memory.

Talking about the management of SQL Server for memory