When learning SQL Server memory management, see some term:memory node,memory clerk,memory object, very confusing, here to record their own understanding, so that follow-up learning
1,numa Architecture and Memory node
Memory nodes are created from hardware NUMA, which is a block of memory within NUMA node and is part of the server's physical memory. The role of memory node is to make the allocation of RAM transferred from Windows to the SQL Server OS level execution.
View Memory Node
Select * from Sys.dm_os_memory_nodes
Memory_node_id=0, not NUMA architecture, belongs to the SMP architecture and each CPU shares a bus.
MEMORY_NODE_ID = 64 is DAC-specific.
Memory clerk, which is used to allocate and reclaim memory. Each memory node has its own DRAM clerk, and only the memories Clerk can access and allocate the node.
There are many types of memory clerk, each of which is used to allocate clerk for a specific purpose. MEMORY_NODE_ID identifies memory node for memory clerk allocation and recovery.
Address of the page allocator. This address was unique for a memory clerk and can being used in sys.dm_os_memory_objects to locate memory objects th At is bound to this clerk.
Select * from Sys.dm_os_memory_clerks
Memory object acquires memory from memory clerk. A memory object requires a memory clerk to allocate its memory.
If a SQL query needs to use memory, then the memory allocation and use is the storage object, and when memory needs to be used, memory clerk must be allocated.
Memory objects is heaps. They provide allocations that has a finer granularity than those provided by memory clerks. SQL Server components use memory objects instead of memory clerks. Memory objects use the page allocator interface of the memory clerk to allocate pages. Memory objects does not use virtual or shared memory interfaces. Depending on the allocation patterns, components can create different types of memory objects to allocate regions of Arbit Rary size.
The typical page size for a memory object is 8 KB. However, incremental memory objects can has page sizes that range from bytes to 8 KB. Page size is not a maximum allocation. Instead, page size is allocation granularity This is supported by a page allocator and that's implemented by a memory CLE Rk. You can request allocations greater than 8 KB from memory objects.
View Memory Object
Select * from Sys.dm_os_memory_objects
The returned field page_allocator_address can join with Sys.dm_os_memory_clerks's page_allocator_address, indicating that the memory object is the memory Assigned by clerk.
The memory object serves the Workder, which is used by the worker, and the worker acquires the memory object and executes the task.
View sys.dm_os_workers, field memory_object_address is the address of the memory object used by the worker.
Select * from Sys.dm_os_workers
To view the entire memory allocation information by request
Select * fromsys.dm_exec_requests RInner Joinsys.dm_os_tasks T onR.task_address=t.task_addressInner Joinsys.dm_os_workers W onT.worker_address=w.worker_addressInner Joinsys.dm_os_memory_objects Mo onW.memory_object_address=mo.memory_object_addressInner JoinSys.dm_os_memory_clerks MC onMo.page_allocator_address=mc.page_allocator_addresswherer.session_id= -
1,sqlos's memory manager and SQL Server ' s Buffer Pool
2, talking about the management of SQL Server for memory
Learn the terminology understanding of SQL Server Memory management