Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 64

Memory Nodes
Memory nodes map directly onto NUMA nodes as described earlier in the chapter, and you can
view details about these nodes on your server using the sys.dm_os_memory_nodes DMV. You will
always have at least one memory node, which has a memory_node_id of 0, and you may have several
if your CPU architecture supports NUMA.
Each memory node has its own memory clerks and caches, which are distributed evenly across
all the nodes (although some objects will only be found in node 0). SQL Server's total usage is
calculated using the sum of all the nodes.
Clerks, Caches, and the Buf er Pool
Memory clerks are the mechanism by which memory caches are used, and the buffer pool is by far
the largest consumer of memory in SQL Server. All three are discussed in this section.
Memory Clerks
Whenever a memory consumer within SQL Server wants to allocate memory, it needs to go through
a memory clerk, rather than going straight to a memory node. There are generic memory clerks like
MEMORYCLERK_SQLGENERAL , but any component that needs to allocate signii cant amounts will have
been written to create and use its own memory clerk.
The buffer pool for instance has its own memory clerk ( MEMORYCLERK_SQLBUFFERPOOL ), as do query
plans ( MEMORYCLERK_SQLQUERYPLAN ), which makes troubleshooting much easier because you can
view the memory allocations made by each clerk and see who has what.
You can view details about all the memory clerks using the sys.dm_os_memory_clerks DMV.
For example, running the following query against a SQL Server 2012 Enterprise Edition instance
running a production workload produced the results shown in Figure 3-8:
SELECT [type],
memory_node_id,
pages_kb,
virtual_memory_reserved_kb,
virtual_memory_committed_kb,
awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY virtual_memory_reserved_kb DESC;
The query orders the results by virtual_memory_reserved_kb , so what you see in the i gure
are the top eight memory clerks ordered by the amount of VAS that they have reserved.
FIGURE 3-8