Shulou(Shulou.com)05/31 Report--

This article introduces the solution to the problem of server crash caused by excessive memory occupation of MSSQL database, which is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Webmaster friends who use MSSQL will be admired by the ability of MSSQL database to eat memory, a small website, after running for a few days, MSSQL will eat all the memory on the server, at this time you have to restart the server or MSSQL to release memory, some people think that MSSQL has a memory leak problem, but in fact, Microsoft has given 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, the Task Manager and performance Monitor will show a steady decline in the physical memory available on the computer until the available memory drops to 4 to 10 MB. Only 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. As long as enough memory is available to prevent memory page swapping (between 4 and 10 MB), the SQL Server buffer pool continues to grow. Like other processes that allocate memory on the same computer as SQL Server, the SQL Server buffer manager frees memory when needed.

SQL Server frees and acquires several megabytes of memory per second, allowing it to quickly adapt to changes in memory allocation. For more information, you can set the upper and lower limits of the amount of memory (buffer pool) used by the SQL Server database engine through the server memory minimum and server memory maximum configuration options.

Determine the correct SQL Server configuration settings before setting the server memory minimum and server memory maximum options.

Note that the server memory maximum option only limits the size of the SQL Server buffer pool. The server memory maximum option does not limit the remaining unreserved memory area, which SQL Server intends to allocate to other components, such as extended stored procedures, COM objects, and unshared DLL, EXE, and MAPI components. Due to the previous allocation, it is normal for SQL Server dedicated bytes to exceed the server memory maximum configuration.

Let's take a look at how to limit MSSQL memory usage:

Step 1: open enterprise management and double-click to enter the MSSQL you want to modify.

Step 2: right-click on the left MSSQL, select Properties, and pop up the SQL Server Properties (configuration) dialog box.

Step 3: click the memory tab.

Step 4, you will see that MSSQL is set to use maximum memory by default, that is, all your memory, according to your needs, set its maximum value.

Step 5: after setting up, shut down the MSSQL service and restart, and the configuration will take effect!

This is the solution to the problem of server crash caused by excessive memory occupation of MSSQL database. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.