Learn the terminology understanding of SQL Server Memory management

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.

2,memory Clerk

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.

Important field:page_allocator_address

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

3,memory Object

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= -

Recommended articles:

1,sqlos's memory manager and SQL Server ' s Buffer Pool

Http://blogs.msdn.com/b/slavao/archive/2005/02/11/371063.aspx

2, talking about the management of SQL Server for memory

Http://www.cnblogs.com/CareySon/archive/2012/08/16/HowSQLServerManageMemory.html

Learn the terminology understanding of SQL Server Memory management