SQL SERVER memory will continue to increase
When the SQL Server database engine in Microsoft? Windows NT? Or Windows? 2000 running, its default memory management behavior is not to obtain a specific amount of memory, but does not produce surplus for Page I / O access to as much as possible under the circumstances memory. To this end, the database engine to get more available memory, while retaining enough available memory to prevent the operating system swap memory.
SQL Server instance starts normally Earn 8 到 12 MB of memory to complete the initialization process. When the instance of the completion of initialization, it will not get more memory, until the user connects to the instance and begin to work load. At this point, the instance is needed constant access to memory to support the workload. As more users connect and run queries, SQL Server will get the extra memory needed to support demand. Access to the memory of the instance will continue until it reaches its target memory allocation, and until it reaches the lower limit of the target will release any memory.
In order to change pages does not produce unnecessary I / O of the acquisition of as much memory, SQL Server are set for each instance of a memory access target, until the computer's physical memory available in 4 MB to 10 MB range. Was chosen because tests showed that the range of Windows NT and Windows 2000 has the smallest memory exchange, until the memory allocation of physical memory available is equal to minus 4 MB. Processing tasks of heavy workload SQL Server instance of physical memory available to retain the lower end of the range (4 MB); work load handling tasks examples of light physical memory available to retain the higher end of the range (10 MB).
Target SQL Server instance with the change of workload changes. As more users connect and generate more work, the examples tend to get more RAM memory to make available to keep the following in the 4 MB limit. When the work load to alleviate, the instances of its target adjusted to 10 MB of free space and free memory to the operating system. To maintain the volume of available space in between 10 MB and 4 MB Windows NT or Windows 2000 to prevent over-implementation of the paging operation, while allowing SQL Server to obtain the greatest possible speed and not to cause additional buffer memory exchange.
Examples of target memory settings with the database page buffer pool size relative to the demand for the available pool. In any real point, aggregate demand depends on the page buffer to meet all current data needed to implement the query page. If the cache memory compared to the number of pages, data pages of high demand, the current of each page in the buffer zone is likely in a relatively short period of time replaced by the new page. This may be "buffer manager" object "page life cycle" performance counter to measure. Relatively small buffer for higher demand, the situation will generate a short lifespan, but the impact is so pure I / O increases because more logic can be read in the pages before references are to be rewritten. To alleviate this problem, the database engine can get more memory to increase the cache size. When the page life-long period, the database engine will be available memory located in the high end (10 MB); and when the page life of short-term, the database engine positioning in the target range of low-end (4 MB).
With the other applications in the computer running SQL Server instance to start, they consume the amount of physical memory available memory causing dropped below the goal of SQL Server. SQL Server instance was the release of their address space enough memory to make available the amount of memory back to the target SQL Server. If there are other applications to stop running Er Shi increase available memory, SQL Server instances will increase the size of its memory allocation. SQL Server can free and acquire several MB per second, bytes of memory, which makes it possible to make rapid changes under the memory allocation adjustments.
You can allow the sql server by setting the maximum memory that can be used to do limit: the minimum and maximum server memory
min server memory and max server memory configuration options set up by the SQL Server database engine, the amount of memory used by the upper and lower limits. Database engine for min server memory is not immediately specify the amount of memory. Database engine started to use only initialize the required memory. As the database engine work load increased, it will continue to support the workload required for memory. Database engine until it reaches the min server memory until specified amount of memory required to release any memory. Once at the min server memory, the database engine will use the standard algorithm (to keep the operating system's available memory 4 MB to 10 MB) of memory required to obtain and release. The only difference is the memory allocation database engine has never fallen min server memory specified level, and never get more than the level specified in max server memory memory.
Database engine to obtain the amount of memory depends on the work placed in the instance of the load. Does not deal with many requests for SQL Server instance may never reach the min server memory.
If the min server memory and max server memory assigned the same value, if the memory allocated to the database engine to achieve the value, the database engine will stop the dynamic release and access to memory.
If the SQL Server instance running on the computer frequently start or stop other applications, start the time required for these applications may be an instance of SQL Server extended memory allocation and release. Also, if SQL Server is a number in a computer running a server application, the system administrator may need to control the distribution of the amount of memory to SQL Server. In these cases, you can use the min server memory and max server memory option to control the SQL Server can use the amount of memory.
He set up a fixed amount of memory (Enterprise Manager)
Set a fixed amount of memory
Expand a server group.
Right-click a server, and then click "Properties."
Click the "memory" tab.
Click the "Use a fixed memory size (MB)", and then fixed the memory slider in the appropriate position.
Note If you use the default settings, then Microsoft? SQL Server? The dynamic configuration memory.
This is the sql server's memory management decisions.