Shulou(Shulou.com)05/31 Report--

This article is to share with you about the MSSQL memory architecture and management, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. MSSQL memory architecture

Compared to Oracle,MSSQL 's memory area is not so clear, but similar to Oracle, MSSQL memory area can also be divided into three parts: buffer pool,query/workspace memory, other cache/memory. Let's give a brief introduction to these three memory areas:

1) Bufferpool:

, case database_id

Else db_name (database_id)

, count (*) * 8amp 1024 as' cached size (MB)'

Group by db_name (database_id), database_id

2) Query/workspacememory:

In PGA,MSSQL like Oracle, query memoy (also known as workspace memory) is used to store temporary results during hashing and sorting during query execution. Although everyone knows the PGA of Oracle, the query memory of MSSQL may not have too many documents to read, therefore, not many people know clearly and know that some internal details and contents, such as MSSQL's background process,memory components, are the status quo, which may be related to many factors, so we don't do much discussion here. However, when you take a closer look at the query plan of the SQL statement in MSSQL, you will clearly see the query memory content related to operations such as hash and sort.

Query memory space can be between 25 and 75 percent of the buffer pool size, but query memory can grow further when there is no memory pressure on the buffer pool. For all small queries whose cost is less than 3 and the query memory requirement is less than 5m, the query memory can reserve the entire query memory5% space for it. A single query can account for up to 20% of the entire query memory to ensure the proper execution of other queries, which is very similar to Oracle's PGA. You can obtain the query memory and related authorization of the query statement through the following SQL statement:

Select session_id,sql_handle,grant_time,requested_memory_kb

From sys.dm_exec_query_memory_grants

, sum (isnull (granted_memory_kb,0)) granted_memory_kb

From sys.dm_exec_query_memory_grants

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, which is used for all memory components in the MSSQL that cannot be placed in the two memory areas mentioned above. Although this part is not as clear as defined in Oracle, it is basically equivalent to other components in SGA in Oracle except buffer pool, of which shared pool is the main one. This part of the area, although it does not occupy much memory, but contains a large number of memory components, is also very important. We can query its relevant information through the following SQL statement:

Select [name], [type], pages_kb,entries_count

Select name,value,value_in_use, [description]

Where namelike'%server memory%'

Page_fault_count,memory_utilization_percentage

Process_virtual_memory_low

The above is what the MSSQL memory structure and management is like. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.