The solution to the problem of server deadlock caused by excessive memory consumption in MSSQL database

Use of MSSQL webmaster Friends will be MSSQL database to eat memory ability to admire admiration, a small site, run a few days later, MSSQL will put all the memory on the server, at this time you have to restart the server or MSSQL to free memory, Some people think that the MSSQL has a memory leak problem, in fact, 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. In addition, Task Manager and Performance Monitor will show that the physical memory available on your computer is steadily dropping until the available memory drops to 4 MB.

The mere presence of this state does not indicate a memory leak. This behavior is normal and is the expected behavior of the SQL Server buffer pool.

By default, SQL Server dynamically increases and shrinks its buffer pool (cache) size based on the physical memory load reported by the operating system. The SQL Server buffer pool continues to grow as long as there is enough memory available to prevent memory page swapping (between 4 and MB). Like other processes on the same computer as SQL Server allocating memory, the SQL Server Buffer Manager frees up memory when it is needed. SQL Server can free and acquire several megabytes of memory per second, allowing it to quickly adapt to memory allocation changes.

More information

You can set the upper and lower limits for 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 you set the server memory minimum and server memory maximum options, review the reference information in the following Microsoft Knowledge Base article titled "Memory":

319942 how to:determine Proper SQL Server Configuration settings (determine the correct SQL Server configuration settings)

Note that the server memory maximum option limits the size of the SQL Server buffer pool only. The server memory maximum option does not limit the remaining reserved memory area, and SQL Server prepares to assign the zone to other components, such as extended stored procedures, COM objects, and unshared DLLs, EXE, and MAPI components. Because of the previous allocations, 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 following article number to view the article in the Microsoft Knowledge Base:

316749 PRB: There may not be enough virtual memory when working with a large number of databases

Reference

SQL Server Books Online; theme: "Server memory minimum and maximum impact"; Memory Architecture ";" Server memory Option ";" SQL Server Memory Pool "

Here we go. How to limit the use of MSSQL memory:

The first step: Open Enterprise Management Double-click to enter the MSSQL to be modified.

Step two: On the left MSSQL right-click, select Properties, pop-up SQL Server Properties (Configuration) dialog box

Step three: Click on the Memory tab.

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

Step Fifth: Set up, turn off the MSSQL service and restart, the configuration can take effect!