MSSQL Memory Architecture and management

1. MSSQL Memory Architecture

Compared to the Oracle,mssql memory area is not so clear, but similar to Oracle, the MSSQL memory area can be broadly divided into three parts: buffer pool,query/workspace memories, other cache/memory. Let's take a brief description of these three memory areas:

1) Bufferpool:

Buffer pool is one of the main components of MSSQL memory, which includes and manages the data buffers of MSSQL. Each buffer in the buffer pool is a 8k-sized memory page (of course, the MSSQL 64-bit system also supports large page, which is not discussed here, the interested classmates can refer to other documents), the same size as the data or index page, so that the entire buffer The pool is made up of this 8k size buffer. You can query the buffer pool information for each database by using the following SQL statement:

Select database_id

, Case database_id

When 32767 and then ' Resourcedb '

else Db_name (database_id)

End as ' database '

, COUNT (*) *8/1024 as ' cached size (MB) '

From Sys.dm_os_buffer_descriptors

Group by Db_name (database_id), database_id

Order BY ' cached size (MB) ' desc;

2) Query/workspacememory:

In Oracle-like Pga,mssql, query Memoy (also known as workspace memory) is used to store temporary results during query execution when hashing and sorting. Although we all know that Oracle's PGA, but MSSQL's query memory may not be too many documents to read, so there is not too many people are clear and know, including MSSQL background process,memory components and other internal details and content, this is the status quo, which may be related to a variety of factors, here do not do too much discussion. However, when you look carefully at the query plan for the SQL statement in MSSQL, you will see clearly how the hash and sort operations relate to the contents of the queries memory.

The allocation of the Query memory is from buffer pool because the MSSQL memory manager is very aware of the overall allocation and usage of its memory, which may be a bit different from Oracle's PGA because Oracle is the process model, in its buffer The conversion and distribution between pool and PGA may be more complicated than MSSQL, which is the characteristic and advantage of the MSSQL threading model, so let's think about it, and don't make too much of it.

The query memory space can account for between 25% and 75% of the buffer pool size, but the query memory can grow further when there is no memory pressure on the buffer pool. For all small queries that cost less than 3 and the query memory needs less than 5M, the query memory can retain the entire query memory5% space. A single query can account for up to 20% of the entire query memory to ensure the normal execution of other queries, which is very similar to Oracle's PGA. You can use the following SQL statement to get query memory and related authorizations for the queries statement:

Select session_id,sql_handle,grant_time,requested_memory_kb

, granted_memory_kb,used_memory_kb

From sys.dm_exec_query_memory_grants

Order bygranted_memory_kb desc;

Select SUM (IsNull (requested_memory_kb,0)) requested_memory_kb

, Sum (IsNull (granted_memory_kb,0)) granted_memory_kb

, Sum (IsNull (used_memory_kb,0)) used_memory_kb

From sys.dm_exec_query_memory_grants

granted_memory_kb desc;

3) Other Cache/memory:

The MSSQL memory area, except for the buffer pool and query/workspace memory described above, is the rest of the Cache/memory section, which is used for all the memory components in MSSQL that cannot be placed in the two memory areas. This section, although not as clear as defined in Oracle, is basically equivalent to removing other components outside of buffer pool from the SGA in Oracle, which is primarily shared pool. This part of the area, while occupying a little memory, but contains a large number of memory components, is also important. We can query its related information by using the following SQL statement:

SELECT [Name],[type],pages_kb,entries_count

From Sys.dm_os_memory_cache_counters

ORDER BY pages_kb Desc;

In addition, log cache/buffer is a memory component that is of concern to us, compared to Oracle, MSSQL's conservative attitude to this memory area, whether it is official website or other information, has not been mentioned in this area. In Oracle, we can easily view the size of the area or easily configure and change the size of the area, however, the area in MSSQL is the opposite, we can neither view nor modify the area, the truth is estimated only Microsoft internal clear. Looking at all the information that can be consulted, we just know that each data in the MSSQL instance has a log cache/buffer, which is a contiguous area of memory, its size is not fixed, the maximum size is 60k, the user cannot intervene in the management and setting of the area, The area by the MSSQL instance automatic dynamic management, only this, but also want to have clear and know this part of the mechanism and management configuration method of the students at any time contact and discussion, first thank you.

2. Dynamic memory Management

MSSQL default memory management behavior dynamic memory management, that is, without causing a system-level memory shortage, as much as possible to obtain its required memory, MSSQL through Windows memory notification APIs to achieve this. This is very similar to ORACLE11G's AMM, except that MSSQL is almost always this way of memory management, and Oracle is only implemented after 11g, which involves both the process model and the memory-sharing implementation of the content and details, which is no longer explored in depth.

When MSSQL starts, it calculates the size of the MSSQL process address space based on factors such as physical memory on the system, number of server threads, and various startup parameters, and MSSQL retains this computed size memory space, but it only acquires the physical memory space required for the current load.

Next, when more users connect and run queries, MSSQL acquires additional physical memory on demand in order to support more load. The MSSQL instance will continue to acquire physical memory until it reaches its max server memory target, or Windows notification no longer has additional own RAM available, at which point the instance gets more memory than min server Memory and Windows notifies free memory in the case of a shortage, the MSSQL frees the RAM.

The Min server memory and max server Memories configuration options establish the upper and lower limits of RAM used by MSSQL instances. MSSQL does not immediately get min server memory to determine the size, and begin to only get the memory of the instance initialization size, and thereafter, as the load on the MSSQL instance increases, it continuously acquires memory to support these workloads, while the MSSQL instance memory reaches min The server memory will not release any acquired RAM, once the min server memory value is reached, the MSSQL instance will take advantage of its internal RAM-related algorithm to obtain and free memory on demand, and, unlike before, the MSSQL instance will no longer release memory to min server Memory determined value, nor does it get RAM above the max server memory value.

In fact, in previous versions of MSSQL2012, Min/max server memory does not refer to all the RAM occupied and managed by the MSSQL instance, but only the buffer pool size in the MSSQL instance, and the other memory components of the MSSQL instance In the later version of MSSQL2012, this changed, Min/max server memory almost includes all the RAM that the MSSQL instance occupies and manages, and the MSSQL instance acquires and frees the memory dynamically on demand based on its load.

When other apps on the same computer running the MSSQL instance start up, they consume memory and free physical memory drops below the MSSQL memory target value, the MSSQL instance begins to adjust its memory consumption. When other apps stop and more memory becomes available, the MSSQL instance begins to increase its memory allocation. The MSSQL instance can release and retrieve several megabytes of memory per second in order to quickly adjust memory allocations. For information about the usage of MSSQL instance memory, you can use the following SQL to query:

Select Name,value,value_in_use,[description]

From Sys.configurations

where Namelike '%server memory% '

Order BY name;

Select physical_memory_in_use_kb,locked_page_allocations_kb,

Page_fault_count,memory_utilization_percentage,

Available_commit_limit_kb,process_physical_memory_low,

Process_virtual_memory_low

From Sys.dm_os_process_memory;

MSSQL Memory Architecture and management