SQL Server 2016 - memory broker clerk DMV ties out well with perfmon

I invest time in trying to fit pieces of memory together, to understand how various types of accounting for database memory work... and work together.

Sometimes its tricky and takes me a while :-)

But today for the first time I tried to tie out sys.dm_os_memory_broker_clerks with equivalent counters from sys.os_performance_counters - the counters we get in perfmon.  I was surprised how easily sys.dm_os_memory_broker_clerks could be reconciled with perfmon.

First, a disclaimer.  The columnstore object pool changes pretty quickly once no queries are running.  And sys.os_performance_counters lags behind sys.dm_os_memory_broker_clerks.  It seems like values are pushed from the source for sys.dm_os_memory_broker_clerks to the source for sys.os_performance_counters on a regular interval - maybe every 2 seconds.  This is most noticeable when the broker clerk for columnstore disappears entirely from sys.dm_os_memory_broker_clerks but is still present in sys.os_performance_counters for a brief time.

OK... here are the queries I used to compare...

;WITH broker_clerks AS
      (SELECT instance_name, counter_name, cntr_value 
       FROM sys.dm_os_performance_counters 
       WHERE object_name = 'SQLServer:Memory Broker Clerks')
SELECT instance_name clerk_name, 8 * [Memory broker clerk size] AS [total_kb], 
8 * [Simulation size] AS [simulated_kb],
8 * [Periodic evictions (pages)] AS [periodic_freed_kb], 
8 * [Pressure evictions (pages/sec)] AS [internal_freed_kb]
FROM broker_clerks
PIVOT (MAX (cntr_value) 
       FOR counter_name IN ([Memory broker clerk size], [Simulation size], [Periodic evictions (pages)], [Pressure evictions (pages/sec)])
       ) broker_clerks_pvt
ORDER BY instance_name;

SELECT   clerk_name, total_kb, simulated_kb, periodic_freed_kb, internal_freed_kb
FROM     sys.dm_os_memory_broker_clerks
ORDER BY clerk_name;

And this is what the results looked like...