Memory misunderstanding of SQL Server

Memory misunderstanding of SQL Server
SQL Server Memory

The read/write speed of the memory is much higher than that of the disk. For the database, it will take full advantage of the memory to cache as much data as possible from the disk to the memory, this allows the database to read and write data directly from the memory, reduce IO requests to the mechanical disk, and improve the data read and write efficiency.

Memory is so important for the database, so we can see the memory as long as it involves database optimization. We usually try our best to optimize the database memory usage, such as enabling AWE, setting the maximum memory, and locking the Memory Page. However, in many cases, we do not know whether a configuration can solve the current problem, or we mistakenly think it will solve the current problem, the reason for this is that we do not fully understand the memory of the database or there is a misunderstanding, in this article, I hope to combine my experience with the content of "SQL Server 2012 Practical Guide for Implementation and Management, I will share with you my understanding of SQL server Memory by referring to [Introduction to common memory mistakes of SQL server.

Guide to SQL Server 2012 implementation and management (Yu ronggang, ZHU HUA) PDF [250 M]:

Misunderstanding 1: SQL Server can only use 4 GB memory in 32-bit operating systems

For this question, we can look at two aspects:

1. in a 32-bit operating system, the virtual address space that an application process can use is 4 GB, of which 2 GB is for the core State (operating system ), in addition, 2 GB is left to the user State (application ). Therefore, the maximum memory available for SQL server is 2 GB.

However, the distribution of the core State and user State can be changed. ini, the advantage of doing so is that SQL server can use 3G memory, but the disadvantage is also obvious, because it reduces the core address space, when the operating system load is large, risks of instability may occur.

(Currently, 32-bit operating systems have fewer cong users, and Microsoft's new operating systems are also 64-bit. This problem will almost never be encountered in the future .)

2. from windows server 2003 to windows server 2008, the operating system can support up to 4 GB memory as long as it is a standard or WEB version and is in a 32-bit architecture, even if the 3G switch is enabled as described in method 1 above, SQL server can only use a maximum of 3 GB memory.

However, if the operating system is Enterprise Edition or data center edition, it is also a 32-bit architecture, but the operating system can support a maximum of 64 GB memory (with PAE enabled ). However, by default, SQL server can only use a maximum of 2 GB of memory.

Therefore, compared with 64 GB operating system memory, 2 GB is a waste of resources for databases. To solve this problem, Microsoft introduced AWE (address window extension) for SQL server (2005, 2008) of the Enterprise Edition and Standard Edition, you can enable AWE to expand the available memory of SQL server, so that it can reach a maximum of 64 GB memory (in fact, it will be a little smaller, because the operating system itself needs to occupy part of the memory ).

To sum up, we can use a table to describe the above two scenarios:

Operating System category	Available address space of SQL Server	Remarks
32-bit	2G
32-bit +/3G switch	3G
32-bit (standard and enterprise) + SQL Server AWE	64 GB (Standard Edition and Enterprise Edition)
32-bit (standard and enterprise) +/3G switch + SQL Server AWE	16 GB (Standard Edition and Enterprise Edition)	This is a reference, and I have never used it in actual scenarios.

 

Misunderstanding 2: After AWE is enabled, all SQL Server functions must use more than 2 GB of memory.

In the SQL server process, not all memory is used by the SQL server data cache, some Third-Party code called by SQL server, other dll loaded in SQL server, SQL server connection, link server, compilation cache, query plan cache, etc. will also use the memory in the SQL server process.

This component or function is different from the traditional data cache application method when applying for memory, because they usually apply for memory pages larger than 8 KB, the memory area is multi-page (previously called memtoleave ). The AWE feature of SQL Server cannot be used for the memory occupied by multi-page. That is to say, in a 32-bit SQL Server, even if AWE is enabled for the database, you can only use 2 GB of memory (user mode ). It can be seen that AWE improves the memory space of the data page buffer pool.

Note: In 32-bit SQL Server, the default size of multi-page is 256 MB + the maximum number of threads configured by sp_configure, X512KB, which is set at SQL server startup.

Note: In 64-bit SQL server, there is no limit on the size of multi-page.

Misunderstanding 3: the SQL Server process does not use memory that exceeds the maximum memory size.

In sp_configure of SQL server, there is a configuration item of max server memory (MB). (In SSMS, right-click the instance and select memory in properties ), many of us think that after this value is set, the SQL server process will not use memory that exceeds this size.

Otherwise, max server memory (MB) is only the upper limit of the buffer pool. However, in the memory of SQL server, not only the buffer pool, but also the multi-page memory, for this part of memory, the max server memory (Mbit/s) cannot be used to limit the capacity. In an environment, we can see that the memory occupied by the sqlservr.exe process will exceed the value set by max server memory (MB.

Note: Generally, multi-page does not occupy much space. Therefore, we usually set max server memory (MB) to equal the memory occupied by SQL server Processes.

Misunderstanding 4: The minimum memory size of SQL Server is set, indicating that the memory size will be used when SQL server is started.

The Minimum memory of SQL server is also configured through sp_configure, And the configuration item is min server memory (MB ). The default value of this configuration item is 0, indicating that the minimum memory is not limited. If it is set to a specific value, such as 2 GB, it indicates that when the memory occupied by SQL server exceeds this size, it will not be lower than this value. Instead of immediately reaching this value when SQL server is started.

The advantage of this mechanism is to prevent SQL server Memory from being squeezed out by the operating system. In this way, when the load increases, the database can use the memory immediately without applying for memory from the operating system. (When applying for a database, the operating system does not need to allocate excess memory to the database, so the database can only be closed ).

Therefore, it is very important to set the minimum memory for the database. The Minimum Memory value recommended by Microsoft is the total Server Memory minus 1-2 GB, which is similar to the maximum memory value. (The premise is that this server creates a database server)

References: http://msdn.microsoft.com/en-us/library/ms178067.as

Misunderstanding 5: the memory occupied by SQL server is very large, which indicates that SQL server has a problem.

Common relational databases all have this feature: Databases occupy the server's memory as much as possible, and the memory occupied by these databases will not be released even if a large portion of the memory is idle, unless the operating system encounters memory pressure, will be re-allocated by the operating system. Therefore, we can see that the memory usage of the database server is usually high.

This is not a problem, but a feature of the database. As described in the first section in this article, the more data is cached in the memory, the higher the read/write efficiency of the database, the faster the response speed. This is the best way to use the database.

However, in many scenarios, the server is not dedicated to databases. To ensure that other applications on the server can run normally, we must set the maximum memory for the database, otherwise, the access to other applications is not smooth due to insufficient memory. I once encountered a case where the HIS server in a hospital must be restarted every few days to ensure that HIS business can be accessed normally. The reason is that the maximum value of the database memory is not set, and the application memory is occupied by the database.

Misunderstanding 6: High memory usage indicates memory pressure on the database server.

(There are many factors that affect SQL Server performance, but this article focuses on the relationship between memory factors and SQL server, so other factors are not included in this article .)

If a DBA determines that the memory is under pressure due to the high memory usage on the database server, this person is not comprehensive enough. As mentioned above, it is natural for databases to "like" The memory. Almost all DB servers have high memory usage (unless the maximum memory is set and the maximum memory is much lower than the server memory ), therefore, we cannot determine the memory pressure of the database.

We usually use some performance counters to monitor the memory usage of the database and make pressure judgments accordingly:

SQLServer: Buffer Manager \ Buffer cache hit ratio

(Generally, the cache hit ratio of OLTP is more than 95%, while that of OLAP is more than 90%.

For good performance)

SQLServer: Buffer Manager \ Page life expectancy

SQLServer: Buffer Manager \ free pages

SQLServer: Memory Manager \ Target Server Memory

SQLServer: Memory Manager \ Total Server Memory

The last three indicators intuitively reflect the memory pressure on SQL server. free pages indicates the memory occupied by SQL server, and how many pages are in the free status, multiply this value by 8 KB to get the available cache size. The larger the value, it indicates that many of the memory occupied by SQL server has not been used yet, so there is no pressure on the memory.

Total Server Memory indicates the Total size (used or not used) of the buffer pool allocated by SQL server ). Target Server Memory indicates the Target Memory size that the operating system can allocate to SQL server. The maximum value is equal to the available address space of SQL server described in error 1, but it will decrease as the pressure on the operating system increases. Under normal circumstances, the Target server memory will be greater than the total server memory, indicating that the buffer pool allocated by SQL server is relatively small and smaller than the Target memory size set for SQL server by the operating system, the database does not have memory pressure at this time. However, as the operating system increases pressure, it will reduce the value of Target Server memory and make it smaller than the Total Server Memory of SQL Server. In this way, SQL Server has to release the occupied cache, reduce total server memory. In this case, there is a memory pressure on the database server.

Misunderstanding 7: increasing the memory will certainly improve the performance of SQL server.

The database will occupy as much memory as possible, but it does not mean that the increase in memory will certainly be the more the better. As mentioned above, if the memory of the database has no pressure for a long time, increasing the memory will not improve the performance.

In addition, in a 32-bit SQL server, when the database is started, a fixed multi-page is allocated for the connection, query plan, third-party dll, and linked server (as described above when AWE is introduced ), because the size of multi-page does not change with the increase of memory, even if the memory is increased, these functions and components will not be used, but will only increase the data cache.

Note: In 64-bit SQL server, there is no limit on the size of multi-page.

Misunderstanding 8: if other applications require memory, SQL server releases some of its memory to ensure that other applications can run normally.

SQL Server does not release the memory occupied by other programs. Only when the operating system encounters memory pressure will it reduce its memory usage according to the requirements of the operating system.

However, if the SQL server enables locking the memory page, the memory will not be released even if the operating system requires it. Locking the Memory Page keeps the memory occupied by SQL server in the physical memory for a long time to avoid being paged to the virtual memory. This is a common practice to improve the performance of SQL server. In the recommended configuration of SQL Server, we often recommend that you do this. To avoid occupying too much memory, you can set the maximum memory usage limit.