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

How the database memory structure is, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The memory requested by the database from the operating system can be divided into two parts:

1. Cache pool memory (data pages and free pages)

two。 Non-cache pool memory (thread / DLL/ connection server, etc.)

Note: the maximum / minimum server memory set by the server instance property refers to cache pool memory

Note:Express can only use 1 GB of memory

Database memory can be divided into three levels

Level1: memory node (Memory Node), which provides interface and implementation of low-level allocator. In NUMA, memory node corresponds to CPU node, and only memory CLERK can access memory node.

Level2: consists of memory CLERK/ memory cache / memory pool. The memory CLERK accesses the interface of the memory node to allocate memory.

Level3: memory objects. SQL SERVER components use memory objects instead of Memory CLERK, and memory objects use the page allocator interface of the memory CLERK to allocate pages.

The database memory is divided into two parts according to the application size.

1. Request memory less than or equal to one unit of 8KB, which is used for caching (singlepage allocator)

two。 Apply for memory larger than one unit of 8KB, which is mainly used for SQL CLR,Linked Server and backup buffer and others. This memory is called Multi-Page OR MemToLeave (multioPage allocator).

MemToLeave reserved memory = ((number of CPU-4) + 256) * 0.5mm 256 is approximately equal to 384MB

To view the memory usage of Memory CLERK, you can call the sys.dm_os_memory_clerks view

To view the memory usage of Buffer pool, you can call the sys.dm_os_buffer_descriptors view

View each database cache

SELECT

DB_NAME (DS.database_id) AS DatabaseName

CAST (COUNT (1) * 8.0 AS INT 1024.0) UsedMB

FROM sys.dm_os_buffer_descriptors DS

GROUP BY database_id

Total memory used by the database: memory mainly used for caching in buffer pool + memory of CLERK singlePage borrowed (stolen) from Buffer pool + memory of MemToLeave (MultiPage)

SELECT

'SinglePage (MB)'

CAST (SUM (C.single_pages_kb) / 1024.0 AS NUMERIC (10Jing 2))

FROM sys.dm_os_memory_clerks C

UNION ALL

SELECT

'MemToLeave (MB)'

CAST (SUM (C.multi_pages_kb) / 1024.0 AS NUMERIC (10Jing 2))

FROM sys.dm_os_memory_clerks C

UNION ALL

SELECT

'Buffer Pool (MB)'

CAST (COUNT (1) * 8.0 AS NUMERIC 1024.0) AS UsedMB

FROM sys.dm_os_buffer_descriptors DS

Or use performance counters to see the memory consumed by SQLServer

SELECT * FROM sys.sysperfinfo P

WHERE P.object_name LIKE 'SQLServer:Memory Manager%'

AND (P.counter_name LIKE 'Target Server Memory (KB)%'

OR P.counter_name LIKE 'Total Server Memory (KB)%')

Memory Object:

It is essentially a heap, allocated by Page allocator, viewed using sys.dm_os_memory_objects, and identified with memory clerk using page_allocator_address

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.