Learn the terminology understanding of SQL Server Memory management

Source: Internet
Author: User
Tags server memory

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


2, talking about the management of SQL Server for memory


Learn the terminology understanding of SQL Server Memory management

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.