Shulou(Shulou.com)06/02 Report--

In the actual scenario, we will encounter all kinds of strange problems, why do we feel strange? because there is no theoretical support to feel strange, SQL Server manages memory itself, and there are few ways we can intervene, so it is difficult to encounter cases of dealing with memory problems on a daily basis. When the original knowledge reserve has become blurred, I can't remember how many times to read the memory management chapter of the practical Guide to SQL 2012 implementation and Management, and share it with my friends.

Some misunderstandings about memory usage

This article clarifies some misunderstandings that users often have about SQL Server memory usage. Understanding this knowledge can help database administrators understand the behavior of SQL Server.

1. There is still a lot of physical memory unused on Windows, which means that SQL Server is not short of memory.

This view is very wrong because:

(1) SQL Server probably sets Max Server Memory, which restricts the ability of SQL Server to continue to apply for memory. (note: the Max Server Memory of SQL Server, a machine that has encountered customer 128GB memory in the actual scenario, is set to 2048MB, which causes serious problems.)

(2) on 32-bit machines, SQL Server may no longer be able to apply for memory due to virtual address space limitations.

For example, on a 32-bit server of 4 GB or greater, SQL Server can only use up to 2 GB of physical memory. Windows typically uses about 500 MB of physical memory, and this machine may have more than 1 GB of free physical memory. This part of the memory is SQL Server without AWE can not be used. Therefore, there is a lot of free physical memory on the server, which only means that Windows is not short of memory, not that SQL Server itself is not short of memory. (this is why it is recommended to replace the older generation of servers and use 64-bit operating systems and SQL Server)

2. The Private Bytes (or Working Set) of the SQL Server process is constantly rising, indicating that SQL Server has a memory leak.

On the contrary, SQL Server has strict restrictions on requesting memory. On 32-bit servers, both Buffer Pool and MemToLeave have a maximum usage limit. When Windows feels memory pressure, SQL Server will actively release memory, so as a process, the chance of memory leak in SQL Serve is very small.

The reason for this confusion is that when SQL Server starts, it only Commit the part of memory it needs to start. As users use, SQL Server will continue to request memory until Windows feels pressure on memory, or until SQL reaches its own limit, until then, SQL Server memory usage does continue to increase. For SQL Server, the more data it caches, the better its performance. This growth is normal. Don't worry. If you don't want SQL Server to use so much memory, you just need to set the upper limit of Max Server Memory.

(note: in fact, many people have mentioned that SQL Server actually ate the memory of all the servers. Is there a memory leak? Or ask if memory is the bottleneck of my system? )

3. The value of Max Server Memory is the maximum memory usage of SQL Server. It is abnormal to exceed this value.

The value of Max Server Memory should be the upper limit of Buffer Pool (this is a very big change for SQL Server 2012 memory management for SQL Server 2005, not the upper limit of all SQL Server memory usage. Since the use of SQL Server memory includes Buffer Pool and MemToLeave, the actual memory usage of SQL Server must be larger than that of Max Server Memory, but under normal circumstances, the use of SQL Server MemToLeave will be much less than that of Buffer Pool. If you control Buffer Pool, you can control the overall memory usage of SQL Server.

(note: it is recommended to set the maximum memory reasonably regardless of the pressure on the memory. PS: I was also surprised when the memory used exceeded the set Max Server Memory.)

4. The total memory usage of SQL Server, which is the value of SQL Server:Memory Manager-Total Server Memory in performance Monitor.

SQL Server collects all the SQL Server-related counter in the performance monitor. Since SQL Server 2005, SQL has integrated all memory requests so that they use the same interface. For third-party code, SQL Server doesn't know how much memory they apply for.

The value of SQLServer:Memory Manager-Total Server Memory is the amount of memory requested by SQLServer's own code. The space value requested by the real SQL Server process will be a little larger than this value. (the specific size is related to the size of the MemToLeave.)

If SQL Server is not enabled, the logical memory and physical memory requested by the AWE,SQL Server process can be seen by Private Bytes and Working Set under Process. These two values will contain all memory expenses, including SQL's own code and third-party code.

If SQL Server turns on AWE, the problem is more awkward. Because Windows has no way to correctly determine how much memory is used by a process that uses AWE memory. We can only use SQLServer:Memory Manager-Total Server Memory to determine the Buffer Pool usage of SQLServer. As for the total amount of memory requested by SQL Server itself (Buffer Pool + MemToLeave), it can be calculated by querying the DMV related to memory, but it is difficult to accurately calculate the memory requested by third-party code.

5. When the system has memory pressure, SQL Server will always release memory automatically.

By default, SQL Server does automatically free memory when the system is under pressure, but there is one exception: SQL Server tries to do a "Lock Page In Memory" action when it starts. If you have this permission to start the account, the action will be successful. Then SQL Server will probably not free memory when other applications on the same server need memory. Therefore, in this case, it is recommended that SQL Server set the upper limit of Max Server Memory.

(note: a lot of Lock Page In Memory materials say that the memory of SQL will not be released, but in fact, when the operating system feels pressure, it will release the memory of SQL, which is also the cause of misunderstanding 6.)

6. SQL Server has a way to bind its memory to physical memory.

SQL Server does want to do this through Lock Page In Memory's approach. However, as a user-oriented application, it is still limited by the kernel mindset. If memory requirements are issued in a kernel mindset, SQL Server will be forced to release its own memory.

7. Increasing the size of MemToLeave can improve the performance of SQL Server.

On 32-bit SQL Server, the default MemToLeave is 256 MB+0.5 MB x (number of Max Thread). If MemToLeave is used up, some important functions of SQL Server cannot be carried out, and even new connections cannot be established, so some SQL Server that requires a lot of MemToLeave, such as SQL Server that often runs Linked Server distributed queries, or SQL Server that runs CLR,Extended Stored Procedur, may have to add some MemToLeave space. This can be done using SQL Server's

A startup parameter of one g is complete. For example, if you want to set MemToLeave to 512 MB+0.5 MB x (the number of Max Thread), you can add the startup parameter-g512.

But what you need to think about is that the virtual address space of SQL Server is only 2 GB, and the more you give MemToLeave, the less Buffer Pool will get. The memory of Data Cache can also be replenished from the extended memory of AWE, but there is no way to make up the Stolen memory in Buffer Pool. So in fact, this is robbing the east wall to make up for the west wall, the key depends on which piece of memory is the most important to the performance and stable operation of SQL Server. There is no need to give more, which will waste resources and affect the performance of Buffer Pool. Only when it is determined that the MemToLeave of SQL Server is really not enough, we should increase it.

8. Increasing physical memory will definitely improve the performance of SQL Server

Since SQL Server likes memory so much, is it certain that the administrator will be able to improve performance by equipping the server with more memory? Most of the time this idea is true, but it is not always true. This is because the additional memory SQL Server is not necessarily available.

(1) the first step is to check which part of the memory has a bottleneck, Database Cache, Stolen, Buffer Pool, or MemToLeave.

(2) to check whether the missing part of memory has reached the theoretical limit, and whether the newly added memory SQL Server is useful, for example, on a 32-bit + AWE server, the maximum amount of Stolen Memory in Buffer Pool is only 1.6 GB. If there is not enough memory in this part, it is useless to add memory. Only by upgrading the system to 64-bit can these restrictions be broken.

It is only when SQL Server's Database Page is short of memory that increasing memory can significantly help performance. If the database is small, commonly used data pages are already cached in memory, and increasing memory will not help performance much.

(note: memory is still a bit difficult to analyze for some primary DBA, and in many cases it is wrong to add memory as long as you see the memory pressure. In many cases, the memory consumption has a lot to do with the statement optimization of your own program.)

9. Is Stolen's memory really stolen?

Many people describe the memory block of Stolen and say that this memory is used by Commit without Reserve, so this memory is called Stolen. Is that wrong? In fact, from the Windows level, for any use of memory, we must follow the process of Reserve and then Commit. For a piece of memory, if it is used directly without Reserve, it will lead to access violation (Access Violation) because of the internal memory management mechanism of SQL Server, all the memory to be used has been Reserve for us. If what SQL has to do is to Commit directly with the address space that Buffer Pool has reserved (Reserve), and this part of memory is not used to store Buffer, it is called Stolen.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.