SQL Server database memory will increase constantly problem analysis _ MySQL

SQL Server database memory will increase problem analysis SQL Server memory will increase

When the SQL Server database engine is in Microsoft? Windows NT? Or Windows? 2000 when running, its default memory management behavior is not to get a specific amount of memory, but to get as much memory as possible without generating additional page I/O. Therefore, the database engine obtains as much available memory as possible and reserves enough available memory to prevent the operating system from switching memory.

The SQL Server instance usually obtains 8 to 12 MB memory at startup to complete the initialization process. After the instance is initialized, no more memory will be obtained until the user connects to the instance and starts to generate workload. In this case, the instance continuously obtains the memory as needed to support the workload. As more users connect to and run queries, SQL Server will obtain the additional memory required for support. The instance will continue to obtain the memory until it reaches its own memory allocation target, and will not release any memory until it reaches its lower limit.

To obtain as much memory as possible without generating additional page feed I/O, each instance of SQL Server sets a memory retrieval target, until the available physical memory of the computer exists in the range of 4 MB to 10 MB. This range is selected because the test indicates that both Windows NT and Windows 2000 have the minimum memory swap until the memory allocation equals to the available physical memory minus 4 MB. The available physical memory of SQL Server instances with heavy workload processing tasks is relatively low (4 MB ); the physical memory available for instances with low workload processing tasks is relatively high (10 MB ).

The target of an SQL Server instance changes with the workload. When more users connect to the instance and produce more work, the instance tends to obtain more memory to keep the available memory below the limit of 4 MB. When the workload is reduced, the instance's target space is adjusted to 10 MB and the memory is released to the operating system. Keeping the available space between 10 MB and 4 MB prevents too many Page breaks in Windows NT or Windows 2000, at the same time, the SQL Server can obtain the highest possible high-speed buffer memory without causing additional exchanges.

The target memory settings of the instance are related to the requirement of the page of the database buffer pool relative to the available pool size. At any instant point, the total demand for the buffer page depends on the number of data pages required for all currently executed queries. If the demand for data pages is large relative to the number of pages in the cache, each page in the buffer zone may be replaced by a new page in a relatively short period of time. This can be measured by the "Page lifecycle" performance counters of the "buffer manager" object. A short life cycle will be generated when there is a high demand for a relatively small buffer, and the pure effect is to increase I/O, this is because pages are often overwritten before they can be referenced by multiple logics. To alleviate this problem, the database engine can obtain more memory to increase the size of the cache. When the page life time is long, the database engine positions available memory at the high end (10 MB) of the target. when the page life time is short, the database engine locates at the low end of the target range (4 MB ).

As other applications are started on the computer that runs the SQL Server instance, the memory consumption causes the available physical memory to fall below the target of SQL Server. The SQL Server instance releases enough memory from its address space to return the amount of available memory to the SQL Server target. If other applications stop running and increase the available memory, the SQL Server instance will increase the memory allocation size. SQL Server can release and obtain several MB of memory per second, which allows it to quickly adjust based on memory allocation changes.

You can set the maximum memory that SQL server can use to limit the impact of the minimum and maximum server memory.

The min server memory and max server memory configuration options establish the upper and lower limits of memory used by the SQL Server database engine. The database engine does not immediately obtain the specified memory volume in the min server memory. When the database engine is started, only the memory required for initialization is used. As the database engine workload increases, it will continue to obtain the memory required to support the workload. The database engine will not release any required memory until it reaches the memory volume specified in min server memory. Once the memory reaches min server memory, the database engine uses a standard algorithm (to keep the available memory of the operating system between 4 MB and 10 MB) to obtain and release the required memory. The only difference is that the database engine never drops the memory allocation to the level specified by min server memory, and never gets the memory that exceeds the level specified by max server memory.

The memory size obtained by the database engine depends entirely on the workload placed on the instance. SQL Server instances that do not process many requests may never reach the min server memory.

If the same value is specified for min server memory and max server memory, once the memory allocated to the database engine reaches this value, the database engine stops releasing and retrieving the memory dynamically.

If other applications are frequently started or stopped on the computer on which the SQL Server instance runs, the time required to start these applications may be extended because the SQL Server instance allocates and releases memory. In addition, if SQL Server is one of several Server applications running on a computer, the system administrator may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control the amount of memory that SQL Server can use.

How to set a fixed amount of memory (Enterprise Manager)

Set a fixed amount of memory

Expand a server group.

Right-click a server and click "properties ".

Click the memory tab.

Click "use fixed memory size (MB)" and place the fixed memory slider in the appropriate position.

Note:

If you use the default settings, Microsoft? SQL Server? Dynamically configure the memory. This is determined by the memory management mechanism of SQL server.