Webmasters who use MSSQL will be amazed by the ability of MSSQL database to eat memory. For a small website, after running for a few days, MSSQL will eat up all the memory on the server. At this time, you have to restart it. The server or MSSQL releases memory. Some people think that MSSQL has a memory leak problem. In fact, it is not. Microsoft gave us a clear explanation:
After you start SQL Server, SQL Server memory usage will continue to rise steadily, even when there is little activity on the server. Also, Task Manager and Performance Monitor will show a steady decrease in the available physical memory on the computer until the available memory drops to 4 to 10 MB.
The mere presence of this state does not indicate a memory leak. This behavior is normal and expected behavior of the SQL Server buffer pool.
By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) based on physical memory loads reported by the operating system. The SQL Server buffer pool will continue to grow as long as enough memory is available to prevent page swapping (between 4 and 10 MB). Like other processes on the same computer as SQL Server allocated memory, the SQL Server buffer manager will free memory when needed. SQL Server can release and acquire several megabytes of memory per second, allowing it to quickly adapt to changes in memory allocation.
You can set upper and lower limits on the amount of memory (buffer pool) used by the SQL Server Database Engine through the Server Memory Minimum and Server Memory Maximum configuration options. Before setting the Server Memory Minimum and Server Memory Maximum options, review the reference information in the section titled "Memory" in the following Microsoft Knowledge Base article:
319942 HOW TO: Determine Proper SQL Server Configuration Settings
Note that the server memory max option only limits the size of the SQL Server buffer pool. The Server Memory Maximum option does not limit the remaining unreserved memory area that SQL Server prepares to allocate to other components, such as extended stored procedures, COM objects, and non-shared DLL, EXE, and MAPI components. Because of the preceding allocation, it is normal for SQL Server private bytes to exceed the server memory maximum configuration. For additional information about allocations in this unreserved memory area, click the article number below to view the article in the Microsoft Knowledge Base:
316749 PRB: May not have enough virtual memory when using a large number of databases
SQL Server Books Online; Topics: "The Impact of Server Memory Minimum and Maximum Values"; "Memory Architecture"; "Server Memory Options"; "SQL Server Memory Pools"
Let's take a look at how to limit MSSQL memory usage in practice:
Step 1: Open the enterprise management and double-click to enter the MSSQL to be modified.
Step 2: Right-click on MSSQL on the left, select Properties, and the SQL Server Properties (Configuration) dialog box pops up
Step 3: Click the Memory tab.
Here, you will see that MSSQL is set to use the maximum memory by default, which is all your memory, according to your needs, set its maximum value.
Step 5: After the setting is completed, close the MSSQL service and restart it, and the configuration will take effect!