SQL server2005 memory too high release method

Recently made a Web site qq.115sou.com, SQL Server in the server memory is very high, add memory, SQL Server and eat the new memory, as if the memory is never enough, how to do?

In fact, this is not necessarily due to excessive SQL Server activity, after starting SQL Server, SQL Server memory usage will continue to rise steadily, even if the server on the activity is very little and will not fall, until the physical memory is only 4 to 10M, this is the SQL Expected behavior of the server buffer pool, normal phenomenon.

However, this behavior reduces the ability of the server to cope with spikes, and when there is a sudden larger activity, it can cause the server to become a machine. To limit the memory usage of the buffer pool, you can open SQL server properties, memory, and on this page, decrease the maximum value and click OK. Click "Run Value", you can find that "maximum" does not change, that the setting is not effective, in fact, the setting changes to restart SQL Server to take effect, restart SQL Server.

The memory here does not refer to the entire memory occupied by SQL Server, but rather to the memory used by the SQL Server buffer pool, so SQL Server actually occupies more memory than this value.

SqlServer2005 Memory allocation

By default, Sqlserver2005 memory changes dynamically based on the availability of resources. The default value for Min server memory is 0,max server memory, which defaults to 2147483647, which can be the Max server The value of memory is specified as minimum intrinsic 16MB. However, if the max server memory value is set to the minimum Sqlserver2005 performance, it cannot even be started. If it fails to start, you can start with the-F startup option and set max server memory to the previous value. When SQL Server dynamically uses memory, he periodically queries the system to determine the amount of physical memory available, and in Server2000, SQL increases or shrinks the buffer adjustment cache based on the activity of the server. To keep the available physical memory between 4MB and 10Mb. Keep this available memory to avoid win server2000 paging. If there is too little memory, Server2000 will free up memory to WinServer2000, If there is too much memory Sql2000 will allocate memory to the buffer pool. SQL adds memory to the buffer pool only when the workload requires it. The dormant server does not increase the memory of its buffer pool. How to manually set up SQL memory: One: set min server memory and man server memory to the same value, which corresponds to the fixed memory allocated to the buffer pool after the value has been reached. Two: set min server memory and man server memory to a range, this method system or data administrator want to configure the SQL instance, but also to consider running other applications on the same machine, it is useful. min server memory guarantees the minimum amount of memory that is available for the buffer pool of the SQL Server instance, which does not immediately allocate the amount of memory specified by min server memory at startup, but unless the min server memory value is lowered, Otherwise, memory usage because the client uses this value, SQL Server cannot free up memory in the buffer pool that has been allocated. SQL Server does not necessarily allocate the specified min server memory, and SQL Server will run in smaller memory if the load of SQL Server does not require the allocation of the RAM specified by min server. max server memory avoids the use of a SQL Server buffer pool for more than the amount of storage that is specified so that the remaining available memory can be used to quickly start other applications. SQL Server does not immediately allocate the amount of memory specified by Max Server memories at startup time. Memory usage increases as SQL Server needs until the value specified by Max Server memory is reached. Unless you increase the value of max server memory, SQL Server cannot exceed this amount. Before you reduce the max server memory value, you can use Performance Monitor to examine the Sqlserver:buffer Manager performance object and note the current value of the Stolen pages and Reserved pages counters. These counters report memory as the number of 8K pages. You should set max server memory to be larger than these two values to avoid out-of-memory errors. For a reasonable minimum max server memory setting (MB), the approximate value is ([Stolen pages] + [Reserved pages])/100. To reduce the max server memory, you may need to restart SQL server to free up memory.

SQL server2005 memory too high release method