Solution to server crash caused by memory usage of the MSSQL database

Webmasters who use MSSQL will be admired by the ability of the MSSQL database to eat memory. After a small website runs for several days, MSSQL will eat up all the memory on the server, at this time, you have to restart the server or MSSQL to release the memory. Some people think that MSSQL has a memory leakage problem. In fact, Microsoft did not give us a clear explanation:

After you start SQL Server, the memory usage of SQL Server continues to rise steadily, even when there is little activity on the server. In addition, the task manager and Performance Monitor will display a steady drop in the available physical memory on the computer until the available memory drops to 4 to 10 MB.

Only this status does not indicate Memory leakage. This behavior is normal and is the expected behavior of the SQL Server Buffer Pool.

By default, SQL Server dynamically increases or decreases the size of its buffer pool (cache) based on the physical memory reported by the operating system. The SQL Server Buffer Pool continues to increase as long as there is enough memory to prevent Page Swap (between 4 and 10 MB. Like other processes on the same computer as the memory allocated by SQL Server, the SQL Server Buffer Manager releases the memory as needed. SQL Server can release and obtain several megabytes of memory per second, so that it can quickly adapt to memory allocation changes.
More information
You can set the upper and lower limits of the memory (buffer pool) used by the SQL Server database engine through the configuration options of the Minimum Server Memory and maximum server memory. Before setting the server memory minimum and server memory maximum options, refer to the following Microsoft Knowledge BaseArticleReference Information in the "Memory" section:
319942 how to: determine proper SQL server configuration settings (confirm the correct SQL server configuration settings)
Note that the maximum Server Memory option only limits the size of the SQL Server Buffer Pool. The maximum Server Memory option does not limit the remaining reserved memory areas. SQL Server is ready to allocate this area to other components, such as extended stored procedures, COM objects, and non-shared DLL, EXE, and mapi components. Due to the allocation above, it is normal that the SQL Server private byte exceeds the maximum server memory configuration. For other information about the allocation in this reserved memory area, click the following article number to view the article in the Microsoft Knowledge Base:
316749 PRB: There may be insufficient virtual memory when using a large number of databases
Reference
Online books of SQL Server; Subject: "Impact of the minimum and maximum Server Memory values"; "memory architecture"; "server memory options"; "SQL Server Memory Pool"

Next we will discuss how to limit the memory usage of MSSQL:

Step 1: Open Enterprise Management double-click to enter the MSSQL to be modified.

Step 2: Right-click MsSQL on the left and select properties. The SQL Server properties (configuration) dialog box is displayed.

Step 3: Click the memory tab.

Here, you will see that MSSQL is set to use the maximum memory by default, that is, all your memory. Set the maximum value according to your needs.

Step 5: After the configuration is complete, close the MSSQL service and restart it. The configuration takes effect!