A clerk is an accountant of memory. SQL Server contains many caches, pools and other memory objects. Each of these is wrapped by a clerk so the various cache, pool and object allocation counts are tracked. For complete details refer to
.
More Information This is a reporting issue and not incorrectly assigned pages. The buffer pool has a
single clerk to track BPool activity so all locked pages and reserved values are accounted for using a single clerk. The NUMA node where the BPool class is initialized will own the clerk and it appears that all the allocations took place on a single node.
The output below appears to show that all the allocations are taking place on node id = 1. The SinglePage Allocator also shows the same value for all nodes. There is a single (SPA) single page allocator that works with the buffer pool so this value is reported the same across all nodes as well.
Memory node Id = 1 KB
---------------------------------------- -----------
VM Reserved 552418724
VM Committed 2945656
Locked Pages Allocated 514134528
MultiPage Allocator 212288
SinglePage Allocator 1076168
(5 row(s) affected)
Memory node Id = 2 KB
---------------------------------------- -----------
VM Reserved 38016
VM Committed 37904
Locked Pages Allocated 0
MultiPage Allocator 3592
SinglePage Allocator 1076168
Memory node Id = 64 KB
---------------------------------------- -----------
VM Reserved 2560
VM Committed 2504
Locked Pages Allocated 0
MultiPage Allocator 2416
SinglePage Allocator 1076168 ~1.2GB
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 537411584
VM Committed 524288
Locked Pages Allocated 514134528
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 5528
Buffer Pool Value
---------------------------------------- -----------
Committed 62284568 ~490GB
Target 62284568
Database 62147426 ~485GB
Dirty 90372
In IO 10
Latched 38
Free 2591
Stolen 134551 ~1.2GB
Reserved 3107
Visible 62284568
Stolen Potential 60662694
Limiting Factor 17
Last OOM Factor 0
Page Life Expectancy 4970
There is nothing in the memory status to show the real breakdown of the BPool (hashed) pages and such for indicating the foreign aspect. This comes from the performance counters. The following query will show the Target Pages, Total pages and Foreign pages for each node. (Taken from my single NUMA system.)
select * from sys.dm_os_performance_counters
where object_name like '%Buffer Node%'
1 MSSQL$SQL2008:Buffer Node Free pages 698
2 MSSQL$SQL2008:Buffer Node Total pages 9924
3 MSSQL$SQL2008:Buffer Node Foreign pages 0
4 MSSQL$SQL2008:Buffer Node Database pages 6027
5 MSSQL$SQL2008:Buffer Node Stolen pages 3199
6 MSSQL$SQL2008:Buffer Node Target pages 298327
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 4256712
VM Committed 44480
Locked Pages Allocated 81920
MultiPage Allocator 25248
SinglePage Allocator 25680
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 4214784
VM Committed 4096
Locked Pages Allocated 81920
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 1464
Buffer Pool Value
---------------------------------------- -----------
Committed 9924
Target 291253
Database 6027
Dirty 150
In IO 0
Latched 0
Free 687
Stolen 3210
Reserved 0
Visible 291253
Stolen Potential 494697
Limiting Factor 13
Last OOM Factor 0
Page Life Expectancy 480
Conclusion
Use the SQL Server performance counters to determine the foreign and target memory distribution across NUMA nodes.
Bob Dorr - Principal SQL Server Escalation Engineer