3.6.4 Optimizing SQL Server memory unitary

3.6.4 Optimizing SQL Server memory unitary
1. Minimum and maximum server memory
These two configurations are used to control the size of available memory for SQL Server. For the smallest memory, when the SQL Server service starts, it does not immediately reach this setting value, but only uses the minimum required memory and then grows as needed, once increased
When the minimum memory setting value is reached, SQL Server will no longer free memory. Maximum memory is used to set the upper limit of memory usage, which can be configured using SSMS or Sp_configu re. It is important to note that the "Max memory" here is actually

Refers to the buffer Pool, in the system, if the memory is not controlled enough to cause free physical memory,

Causes Windows to reduce the working set of SQL Server.

How do I calculate the appropriate maximum memory? You can refer to the following information.

(1) Monitoring the maximum memory usage of SQL Server can be achieved through Performance Monitor mssql$<instance>:memory manager\total Server (k.b) Counter to monitor the overall SQL Server usage of buffer pool. If the physical memory required by SQL Server is super-

After the existing available quantity, this value is reduced, and when the memory is freed, the value is increased. Can be opened in a

Set this value down a bit and then adjust it appropriately by monitoring it.

(2) SQL Server's potential maximum memory usage

When considering potential use, it is important to call the connection server and the external stored procedure in the late

This part of the content may be added in a non-planned manner. In general, each thread will use 0.5MB or

2MB (64-bit) of memory, but also to ensure that about 512MB can be used for this part of the use.

In addition, some large enterprises may use third-party backup software, anti-virus software, etc., which can also affect the SQL

Memory usage of the server. It is best to reserve 1 3GB of memory for these software.

2. Check if the maximum memory configuration is reasonable

When setting up a new server, how do you decide what is the maximum server memory to configure? The most immediate answer is:

set from low start and then periodically monitor (start with a shorter monitoring cycle to find the problem as quickly as possible ) and

Adjust as needed. Or use performance counters to monitor, such as through Mssql$<instance>:buffer manager\page life expectancy (PLE) and memory\available Mbytes to monitor.

The ple counter is used to represent SQL Server The amount of time that the data is cached in memory , in the ideal case the more the longer the better, this is one of the memory pressure indicators. if less than 300s, it is necessary to check the indicator available Mbytes . Available MBytes indicates how much physical memory is currently not being used on windows. The standard recommended by foreign experts is to retain at least 100MB. Of course, do not consider the minimum standard as the minimum configuration, as far as possible to ensure that the GB level

Available memory.

If ple is low, but the amount of available memory is high, you should increase the maximum server memory so that you can

Increase the time of ple. Conversely, if the available memory is low but ple is high, you need to reduce the maximum memory configuration

To release memory to Windows. The following is a partial configuration example. -max server memory 30GB, server has 32gram,ple average of 10000, available memory is

90M B, the minimum 500MB maximum server memory is reduced.

-max server memory 46G B, server has 50G RAM, PLE flat mean value is 1 0. available memory is

1500MB, you should increase the maximum server memory by 1000MB.

-Maximum server memory 60G B, Server has 60GRAM, ple average is 5 0, usable memory is 20M,

Then you should reduce the 100MB maximum memory, or add more RAM (the most straightforward method).

3. Lock Memory Pages

Locking Memory Pages (lock pages in memory, LPIM) is a question that occurs between Windows and SQL Server

Solution, in older versions (such as Windows Server 2003 and previous versions), the results are poor. If there is not enough physical memory on the operating system to support other requests, it is mandatory to reclaim the internal

Save. This is a devastating attack on SQL Server's memory allocation. Similar words may be seen in the SQL Server error log .

A significant part of the SQL Server process memory has the been paged out. This could result in a performance degradation. duration:0 seconds. Working Set (KB): 1086400, committed (KB): 2160928, Memory utilization:50%.

starting with Windows Server , this phenomenon has been significantly improved, but it will also appear. For such situations , the maximum server memory can be resized to the appropriate size, ensuring that Windows has enough memory to run other applications . You can also use SQL Server to lock pages in memory. If LPIM is enabled, the Buffer Pool page of SQL Server is "locked" and theoperating system is not allowed forcibly retracted = after the page is locked, this part of the space will not be counted as available in human memory. However, only the pages allocated by the Buffer pool of SQL Server can be locked, and the operating system can still reclaim other memory, including SQL Server- dependent process memory.

3.6.4 Optimizing SQL Server memory unitary