SQL Server Memory Management [Image and text]

Understanding the memory management of SQL Server is the basis for SQL Server troubleshooting and Performance Tuning. This article describes the memory management principles of SQL Server.

Secondary storage (Secondary storage)

For computers, the storage system is hierarchical. The closer the CPU, the better the speed, but the smaller the capacity (1 ). For example, the traditional computer storage architecture is close to the CPU in sequence: registers in the CPU, level 1 cache, level 2 cache, memory, and hard disk. However, a storage system farther away from the CPU will be an order of magnitude larger than the previous storage system. For example, a hard disk is generally an order of magnitude larger than its current memory.

Figure 1. Computer Storage System

Therefore, for SQL Server, the memory configured by the normal production system usually cannot load all data, so it involves Level 2 storage, that is, disks. As the final mechanical storage component of modern computer systems, the disk needs to move the head to read data (for details about the disk principle, refer to an article I wrote earlier ), in addition, because the data accessed by the database is usually randomly distributed in various locations on the disk, if frequent disk reading requires frequent head movement, this performance will be very low.

It can be seen from the computer body storage architecture that the computer must first read the memory for all operations on data on the hard disk, therefore, reducing access to disk I/O by taking advantage of the memory buffer will be the key to improving SQL Server performance. This is also the starting point of this article.

SQL Server engine, a self-adjusting Engine

Because SQL Server has always been oriented to the small and medium-sized enterprise market, the SQL Server storage engine is designed as a product that can be used without too many configurations, thus reducing deployment costs, however, this is why many people have always criticized Microsoft for making too few configurations open. There is almost no directly configurable space for how SQL Server uses memory. The only available configuration is whether AWE is used and the maximum or minimum memory occupied by the instance, as shown in figure 2.

Figure 2. SQL Server controllable memory options

For specific SQL Server memory usage, such as how much memory is allocated to the execution plan cache and how much buffer is allocated to the data, these cannot be adjusted through configuration. This is why many developers of other technologies are excited about the advantages of Microsoft's developers. In my opinion, although SQL Server provides few controllable configurations, however, in many cases, you can perform "indirect" configuration with familiarity with the principle. This also requires some understanding of the principles of Windows.

How does SQL Server use memory?

The SQL Server storage engine is a Windows process. Therefore, SQL Server uses the same memory as other Windows processes and needs to apply for memory from Windows. After applying for memory from Windows, SQL Server memory can be roughly divided into two parts: Buffer Pool memory (data page and idle page), non-buffer memory (thread, DLL, linked Server, etc ). The buffer pool memory occupies most of the memory usage of SQL Server. The memory occupied by the buffer pool is also set in Figure 2. The memory occupied by sqlservr.exe may be larger than the maximum memory set in Figure 2.

Another point is that the memory usage of SQL Server is characterized by the amount of memory used and will not be released after it is used (unless the Windows Memory pressure is reported ). For example, the Development Server in my company shows memory usage when there is almost no load, as shown in 3.

Figure 3.memory usage of the SQL Server process

We can see that when the CPU load is 0, the memory occupies 13 Gb. This is because the memory applied to Windows from SQL Server has not been released.

The memory size that SQL Server can use is determined by the following factors:

1. Physical memory size

2. Restrictions on the memory of the installed Windows Version (for example, windows server 2008 Standard Edition only supports 32 GB of maximum memory)

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

4.2 configure the memory usage of SQL Server

5. SQL Server version (for example, express version can only use 1 GB of memory)

Layer-3 memory allocation for SQL Server OS

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

Figure 4. SQL Server OS Memory dependency

Memory Node

The bottom layer is the Memory Node, which is used to transfer the allocated Memory from Windows to the SQL Server OS. Each SQL Server instance usually has only one Memory Node. The size of a Memory Node depends on the hardware configuration of the NUMA architecture. We can see some Memory Node information through dbcc memorystatus, as shown in Figure 5.

Figure 5. view Memory Node Information

We can see that the applied memory size can be divided into two parts:

1. Apply for memory units smaller than or equal to 8 KB. These memories are used for caching. (SinglePage Allocator in figure 5)

2. Apply for a memory unit larger than 8 KB. These memories are called Multi-Page (or MemToLeave) (MultiPage Allocator in Figure 5)

The reason why MemToLeave is called MemToLeave is that although most of the memory of SQL Server is used in the buffer zone, some continuous memory is required for SQL CLR, linked server, backup buffer, and other operations, when you start an instance, the 32-bit SQL Server retains a part of the continuous virtual IP address (VAS) for MultiPage Allocator. The specific reserved quantity can be calculated using the following formula:

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

Memory Clerk

Let's take a look at Memory Clerk, which is used to Allocate Memory and classify the Memory allocated by Allocate. The following statement can be executed in a simple way, as shown in 6.

Figure 6. Classification by Memory Clerk type

Note: As shown in figure 4, Memory Clerk is only part of the allocated Memory, and the other part is the data cache (Buffer Pool)

Buffer Pool

Before you start to talk about the Buffer Pool, you should first talk about the virtual memory.

In Windows, each process has a Virtual Address Space VAS (VAS). The 32-bit system is the power of 2 to the power of 32, that is, 4 GB, which is classified as two parts by Windows, some are used in Windows, and the other is used in applications. The virtual memory is not the actual physical memory, but the ing of the physical memory. When the physical memory does not point to the content of the virtual memory, page-missing interruptions are generated and some pages are replaced with the memory, then read the required part from the hard disk to the memory. For this part, read an article I wrote earlier: Talking about the memory management of the operating system.

Therefore, when the Buffer Pool is used to Buffer data pages, it will reduce access to disks when reading data in the future.

This Buffer Pool is the space occupied by setting the maximum and minimum Server Memory in Figure 2. This minimum value does not mean that SQL Server can occupy so much memory at startup, but will not be released once the SQL Server Buffer Pool usage exceeds this value.

In dbcc memorystatus, we can see the Buffer Pool information, as shown in 7.

Figure 7. Buffer Pool Information

When an SQL Server instance is started, the VAS address space reserved by the Buffer Pool depends on multiple factors: including the actual physical memory and the SQL Server is 32-bit or 64-bit (this limit is 32-bit and 4 GB, and a half is required for Windows and MemToLeave space ), for the physical memory actually used by SQL Server, you can view it in the following statement, as shown in 8.

Figure 8. view the physical memory used by the Buffer Pool

The Buffer Pool constantly applies for memory as needed. If the Buffer Pool is required, the Buffer Pool will continuously consume the memory until Windows notifies SQL Server that the memory is too low. Otherwise, the Buffer Pool occupies the memory and will not be released.

In addition, the size of the page allocated by the Buffer Pool is the same as that of the SQL Server OS page, that is, 8192 bytes, when the other part of SQL Server needs to borrow memory from the "Buffer Pool", it can only be borrowed in 8 K units, and the physical memory in this part is not continuous, this sounds like the Buffer Pool memory management system, which can be understood as this, because the Buffer Pool directly uses the virtual or awe sqlos interface instead of using any SQL Server page allocator.

Therefore, the memory occupied by SQL Server can be roughly estimated using this formula: Memory occupied by the buffer pool + Page occupied by the buffer pool + non-buffer pool memory allocated by multiPageAllocator, 9.

Figure 9. approximate estimation of memory occupied by SQL server

Memory Object

A menory object is essentially a heap, which is allocated by Page Allocator. You can use sys. view the dm_ OS _memory_objects DMV. This DMV shows the Page_Allocator_Address column, which is the identifier of Memory Clerk, indicating the Memory Object allocated by the Memory Clerk.

Memory bottleneck of 32-bit SQL Server

According to some basic principles described earlier in this article, since 32-bit SQL Server uses VAS for address allocation, the addressing space is limited to 4 GB, half of the 4 GB memory is allocated to Windows, so that the Buffer Pool can only use 2 GB of memory, which makes 32-bit SQL Server unusable even if there is excess physical memory.

One solution is to reduce the default 2G to 1G occupied by Windows, so that the memory available for SQL Server can be changed to 3G. You can set the increaseuserva option by typing BCDEdit/set in the command line of Windows Server 2008. The value is 3072 MB. For Windows Server 2003. add the/3 GB startup parameter to ini.

Another way is to use AWE (Address WindowExtension) memory allocation. AWE uses Physical Address Extension PAE to add four bits, so that the 32-bit CPU Address range is increased to the power of 2's 36, that is, 64 GB. The problem of insufficient addressing range is basically solved.

VirtualAlloc and AllocateUserPhysicalPages

VirtualAlloc and AllocateUserPhysicalPages are the methods used by SQL Server to apply for memory from Windows. By default, all memory required by SQL Server uses VirtualAlloc to apply for memory in Windows. This kind of application is at the operating system level, that is, the corresponding virtual memory. This causes a problem. All memory allocated through VirtualAlloc can be replaced with virtual memory when Windows is facing memory pressure. This causes IO usage problems.

The memory applied for using AllocateUserPhysicalPages directly matches the underlying Page Table. Therefore, the memory applied for using this method will not be replaced by the memory. In the case of 32-bit SQL Server, by enabling AWE to allocate memory, the data cache part of the buffer pool will use this function, while MemToLeave and Buffer Pool memory (mainly execution plan cache) still passVirtualAlloc for memory allocation.

Therefore, before enabling the memory allocation through AWE, SQL Server requires the corresponding permissions. Otherwise, an error is reported in the log, as shown in 10.

Figure 10. error message when AWE is enabled but corresponding permissions are not enabled

You can set this permission for the account that starts SQL Server in the Group Policy, as shown in Figure 11.

Figure 11. Lock Page In Memory)

64-bit SQL Server problems

64-bit Windows basically does not have the above memory problem, but you still need to note that by default, 64-bit SQL Server still uses VirtualAlloc for memory allocation, this means that all allocated memory will be replaced by pages when Windows is under pressure, which may cause jitter (Buffer Pool Church ), in this case, the pages in the SQL Server Buffer Pool are constantly exchanged into the hard disk, resulting in a large amount of IO usage (you can use sys. dm_exec_query_memory_grants: DMV to view and wait for memory queries). Therefore, 64-bit SQL Server allocates the Date Page in the Buffer Pool through AllocateUserPhysicalPages to avoid this problem. Unlike 32-bit SQL Server, 64-bit SQL Server does not need to enable AWE. You only need to enable "Lock Page In Memory" as shown In 11.

However, this exposes another problem because SQL Server locks the Memory Page. When Windows Memory is in a hurry, SQL Server cannot respond quickly to Windows Memory (of course, the non-data cache and MemToLeave in the Buffer Pool are still acceptable, but they are often insufficient because the memory consumption is small compared with the Data Cache ), because SQL Server features memory usage, it is very likely that Windows may be unstable or even crashed when it fails to respond to low Windows Memory. Therefore, after "Lock Page In Memory" is enabled, we need to limit the Memory usage of the SQL Server Buffer Pool. As mentioned In Figure 2 above, we will not detail it here.

Another problem is that when Buffer poolallocates memory through allocateuserphysicalpages, the memory occupied by sqlservr.exe that we see in the task manager only contains the non-Data Cache part and MemToLeave part in the Buffer Pool, instead of Data cache.exe, it seems that sqlservr.exe can only occupy several hundred megabytes of memory and dozens of GB of memory. In this case, check the SQL Server: Memory Manager \ Total Server Memory counter in perfmon.exe to find the Memory occupied by SQL Server.

Summary

This article describes the basic principles of SQL Server Memory Management and the memory usage of SQL Server. for SQL Server performance optimization, understanding the memory usage is a key part, many IO problems may be caused by memory.

Click here to download the PDF version of this article