30 seconds, two ways to solve the memory management problem of SQL Server

Time:2021-1-27

Today, I’d like to talk about the memory management of SQL server,How does SQL server use memory?Only after we know how to use it can we talk about how to manage it.

In short,SQL Server database memory use principle is how much memory will occupy how much memory, will not automatically release memory. The reason is that its storage engine itself is a process under windows, so when using memory, it is the same as other windows processes, and they all need to apply for memory from windows. After applying for memory, the memory use of SQL server can be roughly divided into two partsOne is buffer pool memory, which is occupied by data pages and free pages; the other is non buffer memory, which is occupied by threads, DLLs, link servers, etc. Among them, buffer pool memory accounts for the majority.

What’s the problem? For exampleWhen executing a SQL with a large result set, the memory will be occupied until the machine memory is full after the data query is taken out(it won’t explode, because you can limit the maximum memory through the graph, and it is still slightly smaller than the server’s memory when it is full). Before restarting the database service, SQL server will not release the memory actively, and there is no way to release it artificially. It occupies the set value of Max server memory all the time, so check the SQL server The basic memory utilization rate of server is relatively high (more than 80%).

How to solve it? The most direct way is: artificialModify the maximum available physical memoryThere are restrictions.

Method 1

The memory occupied by the buffer pool is set by the maximum server memory and the minimum server memory in Figure 1, so sqlservr.exe The memory occupied (as shown in Figure 2) may be larger than the maximum memory set in Figure 1. The administrator user logs in to the database client (Microsoft SQL Server Management Studio), right-click on the instance name (root node of tree menu, column of login name displayed), select property > memory, and set “maximum server memory” to adjust the memory (unit: MB) according to the actual situation, such as adjusting to about 70% of the current total server memory utilization.

30 seconds, two ways to solve the memory management problem of SQL Server

Figure 1 SQL Server controllable memory options

30 seconds, two ways to solve the memory management problem of SQL Server

Figure 2 SQL server process memory usage

Method 2

If you are using Huawei cloud RDS SQL server, the solution is simpler. You only need to modify the max server memory parameter value in the console instance management, and you do not need to restart the database after modifying the parameter. It has to be said that the use of cloud database is more convenient and clear. For more use of Huawei cloud RDS SQL server, please refer to the official documents:https://support.huaweicloud.com/rds/index.html

30 seconds, two ways to solve the memory management problem of SQL Server

Click follow to learn about Huawei’s new cloud technology for the first time~