Errors in SQL Server memory understanding

SQL Server memory Understanding

Memory read and write much larger than the disk, for the database, will make full use of the advantages of memory, the data as much as possible from the disk cache to memory, so that the database can read and write data directly from memory, reduce the IO request to the mechanical disk, improve the efficiency of data read and write.

Memory is so important to the database that we can see the memory as long as it is involved in database optimization. We usually try to optimize the use of database memory, such as open awe, set the maximum memory, lock memory pages, etc., but in many cases, we do not really know whether a configuration will solve the current problem, or we mistakenly think to solve the current problem, This occurs because we do not understand the memory of the database is not thorough or understand the existence of misunderstanding, this article I would like to combine their own experience and the SQL Server 2012 implementation and management of the actual practice guide, through the "Introduction of SQL Server Common memory error" Way to share my understanding of SQL Server memory with you.

Myth One: SQL Server can only use 4G memory on a 32-bit operating system

For this problem, we look at two aspects:

1. In a 32-bit operating system, the application process can use a virtual address space of 4G, where 2 G is given the kernel mentality (operating system), and 2G is left to the user state (application). As a result, SQL Server can actually use only 2 g of memory.

However, this kernel mindset and user-state distribution can be changed when the operating system's startup files (Windows Server 2003 of BOOT. ini) when the 3G switch is turned on, you can increase the user-state memory to 3G, and only 1 g to the kernel mentality, the benefit is that SQL Server can use 3G of memory, but the disadvantage is obvious, because reduced the nuclear mentality of the address space, The risk of instability can occur when the operating system load is large.

(There are currently fewer customers using 32-bit operating systems, and Microsoft's new operating system is 64-bit, which is almost impossible to Cong.) ）

2. From Windows Server 2003 to Windows Server 2008, as long as the standard or Web version, and 32-bit architecture, the operating system can only support 4G of memory, even if the 3G switch is turned on by the method described in 1 above, the SQL The server can only use up to 3G of memory.

However, if it is an Enterprise Edition or a datacenter version of the operating system, the same 32-bit architecture, the operating system can support up to 64G of memory (PAE-enabled premise). However, by default, SQL Server can only use up to 2G of memory.

Therefore, compared to 64G of operating system memory, 2G to the database is simply a drop in the bucket, waste of resources. To address this issue, Microsoft introduced the AWE (Address window extension) feature for Enterprise and Standard editions of SQL Server (2005, 2008), and you can extend the available memory of SQL Server by turning on AWE to make it up to 64G of memory (actually smaller, Because the operating system itself needs to occupy some memory).

Summing up, you can use a table to describe the above two scenarios:

Operating system Categories	SQL Server available address space	Note
32 Guests	2G
32-bit +/3G switch	3G
32-bit (Standard Edition, Enterprise Edition) +sql Server AWE	64G (Standard Edition, Enterprise Edition)
32-bit (Standard Edition, Enterprise Edition) +/3g switch +sql Server AWE	16G (Standard Edition, Enterprise Edition)	This is a reference material, I have not used the actual scene.

Myth two: After awe is turned on, all features of SQL Server must be able to use more than 2G of memory.

In SQL Server processes, memory is not all used by SQL Server's data cache, some third-party code that is called through SQL Server, other DLLs that are loaded inside SQL Server, SQL Server connections, linked servers, compiled caches, Memory in the SQL Server process is also used by the query plan cache.

This component or feature differs from traditional data caching requests when requesting memory because they typically request a memory page larger than 8KB, which is multi-page (formerly called MemToLeave). For multi-page-occupied memory, it is not possible to use the AWE feature of SQL Server, that is, in 32-bit SQL Server, the database can only use 2G of memory (user state) Even if AWE is turned on. This shows that AWE has increased the memory space of the data page buffer pool.

Note: In 32-bit SQL Server, the default size of multi-page is the maximum number of threads configured for 256mb+sp_configure x512kb, which is already set when SQL Server starts.

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

Myth Three: The SQL Server process does not use memory sizes that exceed the maximum memory settings.

In SQL Server sp_configure, there is a max server memory (MB) configuration item (right-click the instance in SSMs, select Memory in the properties), and many of us think that setting this value after SQL The server's process does not use more than this size of memory.

In fact, max server memory (MB) is only the upper limit of the buffer pool. However, in SQL Server memory, not only the buffer pool, but also the memory of multi-page, for this part of memory is not limited by the max server memory (MB), so in the real world, We may see sqlservr.exe this process will consume more memory than the max server Memories (MB) set.

Note: In general, multi-page does not occupy a large space, so we typically equate max server memory (MB) with the size of the RAM that the SQL Server process consumes.

Myth Four: Set the minimum memory for SQL Server, which means that the size of memory is used when SQL Server starts.

The minimum memory in SQL Server is also configured by sp_configure, and the configuration item is min server memories (MB). The configuration item defaults to 0, which means that the minimum memory is not qualified and, if set to a specific value, such as 2G, indicates that when SQL Server occupies more than this size, it will no longer be below this value. Instead of saying that this value is immediately reached when SQL Server is started.

The benefit of this mechanism is to avoid SQL Server's memory being squeezed by the operating system, so that when the load comes up, the database can immediately use the memory without requiring the operating system to request memory. (When the database request is not necessarily the operating system has extra memory allocated to the database, then the database can only break the dishes).

Therefore, it is very important to set the minimum memory of the database reasonably. Microsoft's recommended minimum memory value is the total server memory minus 1-2g, which is similar to the maximum memory value. (provided that the server makes the database server)

Reference: Http://msdn.microsoft.com/en-us/library/ms178067.as

Myth Five: SQL Server occupies a particularly large memory, which indicates a problem with SQL Server.

Common relational databases have this feature: The database takes up the server's memory as much as possible, and the memory that is consumed is not freed even if the operating system encounters memory pressure before it is reassigned by the operating system. So we can see that the memory usage of the database server is generally high.

This is actually not a problem, but is a database characteristics, as the first paragraph of the article said, only the more data cached in memory, the database read and write efficiency will be higher, the response speed will be faster. This is the best way to use the database.

However, in many scenarios, the server is not dedicated to the database, to ensure that other applications on the server can function properly, we must set the maximum memory for the database, or other applications will be due to lack of memory access is not smooth. I have encountered a case, a hospital's his server must be restarted every few days to ensure his business can be normal access. The reason is because the database memory is not set to the maximum value, causing the application's memory to be squeezed by the database.

Myth Six: High memory utilization indicates that the database server is experiencing memory pressure.

(There are many factors that affect SQL Server performance, but because the center of this article is talking about memory factors that are related to SQL Server, other factors are not within the scope of this article.) ）

If a DBA makes a memory-pressure decision because of the high memory usage of the database server, it means that the person is not comprehensive enough. As we have already said, the database "likes" memory is natural, almost all DB servers have a high memory usage problem (unless the maximum memory is set and the maximum memory is much lower than the server memory), so we cannot judge the memory pressure of the database accordingly.

We usually use performance counters to monitor the memory usage of the database and make a stress judgment:

Sqlserver:buffer Manager\buffer Cache Hit ratio

(typically requires OLTP cache hit ratio more than 95%, OLAP requires more than 90% to be called

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 above three indicators are the most intuitive to reflect the memory pressure of SQL Server, free pages indicates that SQL Server occupies memory, how many pages are in the state, the number is multiplied by 8KB to obtain the size of the available cache, the larger the value, the SQL Many of the memory that the server occupies is not yet in use, so the memory is not stressed.

Total Server memory represents the overall size (used and unused) of the buffer pool allocated by SQL Server for itself. Target Server memory indicates that the operating system is able to allocate to SQL Server the size of the target RAM, which is approximately equal to the size of the SQL Server available address space described in "error one", but decreases as the operating system pressure increases. Under normal circumstances, target server memory will be larger than total server memory, which means that SQL Server allocates a smaller buffer pool than the operating system has set for SQL Server. The database does not have memory pressure at this time. However, as the operating system pressure increases, it will reduce the value of target Server memory to less than the total Server memory of SQL Server, so that SQL Server has to free up the consumed cache, reducing total Server memory, in this case, indicates that the database server is under pressure.

Myth Seven: Increasing memory will certainly improve SQL Server performance.

Although the database will occupy as much memory as possible, it does not mean that the more memory is necessarily the better, as stated above, if the memory of the database for a long time there is no pressure, the increase in memory will not lead to performance improvement.

Also, in a 32-bit SQL Server, a fixed-size multi-page (described earlier in the introduction of AWE) is allocated at database startup for connections, query plans, third-party DLLs, linked servers, and so on, because the size of multi-page does not change as memory increases , so even if the memory is increased, it will not be beneficial to these functions, components, but only to increase the data cache.

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

Myth Eight: If other applications also need memory, SQL Server frees up some of its own memory to ensure that other applications are functioning properly.

SQL Server does not release its own memory for other programs, and only reduces its memory footprint based on operating system requirements when the operating system encounters memory pressure.

However, if SQL Server has the Lock memory page enabled, its memory will not be freed even if the operating system requires it. Because locking memory pages causes SQL Server to retain memory for a long time in physical memory, avoiding paging into virtual memory, which is a common practice for improving SQL Server performance. In the recommended configuration for SQL Server, we often advise customers to do so. Not enough to avoid too much memory consumption, you can limit the use of memory by setting the maximum memory.

Errors in SQL Server memory understanding