How to reasonably set the memory to run the database and other programs simultaneously

I. Memory Management Mechanism of the SQL Server database.
When the database is started, the database engine does not immediately capture the memory size specified by the min parameter in the memory. The SQL Server database will not do this kind of thing. When the system starts, the database buffer pool only captures the memory required for initialization, as shown in. After the database is started, the workload of the database engine increases as the business volume increases. At this time, the database engine will continue to obtain the memory required to complete the work. The Database Engine Buffer Pool does not release any memory it obtains until the min limit is reached. For example, the initial memory of the database is 20 MB, and the specified min memory is 50 MB. The memory requirement may reach 40 MB because the user needs to query a report. After the query job is executed, the actual memory usage of the Database System Buffer Pool may only need 20 mb. But as long as the value does not reach 50 m. The database system will not return 20 mb of memory to the operating system, but will continue to occupy the memory for subsequent jobs.
If you need to call functions or processes in the database system, the memory requirement may exceed the specified minimum memory volume, for example, 80 Mb. At this time, the database engine will follow certain standards Algorithm To obtain and release the memory as needed. When you need to use a memory larger than 50 MB, the memory will be obtained according to the standard algorithm provided by the system as long as the maximum memory limit is not reached. What is the standard algorithm? This is not what our database administrators need to pay attention. After this process or function call is completed, the memory usage may be reduced to below the minimum memory usage, for example, 40 MB. In this case, the database engine releases memory and releases unused memory to other applications. Program . However, this release has a limit. The Database Engine Buffer Pool never reduces the memory allocation to the level specified by the Minimum Memory. That is to say, at this time, the database will release up to 30 mb of memory, leaving 50 MB of memory space for itself, even if it may only need 40 MB of memory space at present. Of course, when capturing the memory space, it will not allocate the memory space higher than the memory space specified by Max memory.
Ii. Impact of memory parameter configuration on other application systems.
On a single platform, multiple applications often need to share the memory. If multiple application systems may be deployed on a single server, the operating system and the SQL Server database must coexist on at least one server. Therefore, the amount of memory space that the SQL Server database can use will definitely have a big impact on other applications.
For example, the SQL Server database system, mail application system, and ERP application server are deployed on one server. If you first start the SQL Server database system and then start the mail application system, the startup speed of the mail application system will be affected. Some applications may need to use a large amount of memory during initialization. During the startup process, the database may use a large amount of memory, and the remaining memory cannot meet the needs of other applications. At this time, other applications will wait when starting, waiting for the database application to release the memory. This will prolong the startup time of other applications.
In addition, some work may be affected. For example, back up the database and email server at the same time. Because the backup job requires a large amount of memory, the memory contention option will occur. Therefore, if the minimum memory of the database is relatively low, the database system can immediately release the memory when the memory is used up, instead of retaining a large amount of temporary memory space for itself. Similarly, if you set the maximum memory size to a small value to meet your business needs, you can leave a large amount of memory space for other applications.
3. Memory Allocation when multiple applications coexist .

If a server runs only one database application, or the database application is relatively simple, and the database memory usage basically does not exceed the minimum amount of memory, you can use the default multi-parameter configuration. However, if a large number of applications or databases are deployed on the same server, the minimum and maximum memory of the database must be configured, so that multiple applications can achieve peaceful coexistence. In addition, if the database itself is relatively simple, but other applications are relatively complex, it may occupy more than 90% of the memory, the database needs to adopt certain protection measures, you need to set the minimum memory and maximum memory, to ensure the memory space required for the normal operation of your database.
1. The database administrator needs to monitor the memory usage and set it based on the monitoring data. Because the operating system and deployed applications are different, and even the services enabled in the SQL Server database are different, the amount of memory used by the database is also different. Therefore, there is no specific standard to describe the minimum memory or the maximum memory size to be configured. Generally, the database administrator needs to track the database server for a period of time to see its memory usage. For example, I suggest that you observe the memory usage every month in the first year after the database system is put into use. The data obtained is of little reference value due to the unstable usage in the first few months. From the sixth month to the last eight months of the database memory usage, we can basically find a reasonable range of Minimum Memory and maximum memory. Then the database administrator can set the maximum and minimum memory values. In the future, if the foreground application has made new adjustments or the database has adopted new functions, you still need to monitor the memory usage to determine a reasonable memory usage range. In general, it is best to set the minimum memory to a smaller value, and the maximum memory consumption to a larger value. In this case, the database engine can be more flexible in memory management. Then adjust the subsequent monitoring data, and slowly adjust the memory configuration to the optimum. Based on the author's experience, it often takes a long time to achieve reasonable memory configuration. However, this process may not be very long for database performance optimization.
2. Analyze the memory usage of the database system, and distribute the traffic to the memory usage peak of other applications. Database functions, processes, complex views, backups, and other jobs all use considerable memory. For example, if an enterprise deploys an ERP system on the SQL Server database, and the system has an "inventory purchase" operation, this requires complicated business logic, complex calculation of inventory, inventory, and packaging quantity must be considered. Therefore, it is best to put this job to run when the server is relatively empty to run it in a wrong way with other applications. For example, when designing an application, you can run the job in the background and run it at pm. Then, the results are displayed before the morning of the next day. In addition, each application needs to back up its own data to prevent unexpected needs. The backup program often needs to use a large amount of memory. Therefore, it is best to run the backup jobs of various applications by mistake. In this way, the memory usage between applications can be reduced.
4. Special Cases of database memory allocation.
If multiple applications are deployed on the same server, the memory contention is inevitable. Therefore, database administrators often need to allocate appropriate memory parameters to the database to ensure that the database can meet its daily operation needs and minimize adverse effects on other applications. In the parameter configuration process, in addition to the above suggestions, you also need to pay attention to the special circumstances in the database memory allocation.
If the minimum memory and maximum memory are set to the same value, what will happen? If they are set to the same value, once the memory allocated to the database engine reaches this value, the database engine will stop dynamically releasing the buffer pool and obtaining the memory. That is to say, the database memory allocation is fixed at this time and will not be dynamically acquired or released based on the database engine load. Obviously, this is not a good configuration for the server used for generation. However, in some special applications, it can achieve better results. If needed Test The relationship between a job and memory in the database may need to be set to the same value. In addition, if the server only runs the database application, you may need to set these two parameters to a higher value to reduce the extra overhead incurred when the database memory is released and obtained.
In addition, some jobs in the database running process are not limited by the maximum memory. For example, the process in the database may obtain the memory specified by the maximum memory option. Some external components of the database can also obtain memory other than the buffer pool. However, in most cases, the database operation is still restricted by the buffer pool memory. Therefore, it is best to leave some room for memory parameter settings.