Condividi tramite

The DMV sys.dm_os_memory_clerks May Show What Appears To Be Duplicate Entries

  • Articolo

In SQL Server 2008 some of the memory based DMVs show memory node id 0 when you might not expect them to.   For example you could see the following on a single, node system.

SQL 2008

memory_clerk_address type name memory_node_id
0x0000000003EF6828 MEMORYCLERK_SQLBUFFERPOOL Default 0
0x0000000005040828 MEMORYCLERK_SQLBUFFERPOOL Default 0

SQL 2008 R2

memory_clerk_address type name memory_node_id
0x0000000003EF6828 MEMORYCLERK_SQLBUFFERPOOL Default 0
0x0000000005040828 MEMORYCLERK_SQLBUFFERPOOL Default 64

Notice that the clerk addresses are different so they really do belong to different NUMA nodes.    The difference is the exposure of the logical memory node id for the DAC node (64).

In order to support the Dedicated Admin Connection it is given a logical node with a bit of dedicated memory.   The node is not associated with any physical NUMA node or CPU and it is given the memory_node_id of 64. 

To understand this a bit better you have to understand how SQL considers memory nodes.   At the SQLOS level a memory node it represented by the physical layout of CPUs to Memory as presented by the operating system.   When looking at SQLOS level DMVs the memory nodes often represent this physical alignment.  Since DAC is a logical implementation it is just assigned a node id by SQLOS.   In SQL 2008 some of the DMVs did not account for DAC and would output the physical memory node association, making it look like you have duplicate clerks on the same node.

There may also be ways you can see what appears to be duplicates in SQL 2008 R2 if you are using SOFT NUMA.   Under SOFT NUMA you can split a physical NUMA node into logical NUMA nodes at the scheduling level for SQL Server.   However, at the SQLOS memory node level it still works with the physical memory layout presented by the operating system.  This allows SQLOS to provide facilities such as node local memory access to one or more logical nodes that may have been configured with SOFT NUMA.

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    March 05, 2012
    Excellent clarification, Thank you. dvc