SQL Server Memory Accounting: Aligning Perfmon & DMVs

Short one for today, trying to get back into the habit of posting stuff here ;-)

I remember staring at all of the SQL Server memory-related counters available in perfmon, and wondering how I was going to make sense of all of them.

I remember the relief when I settled on "total = database + stolen + free", which aligned with the formula I usually used when evaluating UNIX server memory: total = file + computational + free.

Here's a link to a post from a few years ago about #SQLServer memory accounting...
Perfmon: SQL Server Database pages + Stolen pages + Free pages = Total pages
http://sql-sasquatch.blogspot.com/2013/09/perfmon-database-pages-stolen-pages.html

And here's a post going through a tricky detail when accounting for memory in AIX...
IBMPower AIX Memory Accounting: Fun with vmstat... Numperm Surprise!!
http://sql-sasquatch.blogspot.com/2014/10/ibmpower-aix-memory-accounting-fun-with.html

Its time to return to squaring the numbers between the DMVs and Perfmon.  I want to make sense of resource_semaphore_query_compile waits; I'll have to evaluate threshold values from sys.dm_exec_query_optimizer_memory_gateways in light of optimizer memory, granted memory, used granted memory, and total stolen memory.  The gateway threshold values aren't available in perfmon so I'd like to grab all of the numbers from the DMVs.  We'll see.

Here's a query I'm using to square up the basics between perfmon and the DMVs.

;WITH calc(counter_name, dmv_calc) AS
 (SELECT 'Target Server Memory (KB)' AS counter_name, 
         CONVERT(INT, sc.value_in_use) * 1024 AS dmv_calc
  FROM   sys.configurations sc
  WHERE [name] = 'max server memory (MB)'
  UNION ALL
  SELECT 'Database Cache Memory (KB)' AS counter_name,
         pages_kb AS dmv_calc
  FROM sys.dm_os_memory_clerks mc
  WHERE mc.[type] = 'MEMORYCLERK_SQLBUFFERPOOL'
  AND memory_node_id < 64
  UNION ALL 
  SELECT 'Total Server Memory (KB)' AS counter_name,
         SUM(virtual_address_space_committed_kb) 
         + SUM(locked_page_allocations_kb) AS dmv_calc
  FROM sys.dm_os_memory_nodes
  UNION ALL
  SELECT 'Stolen Server Memory (KB)' AS counter_name,
         SUM(allocations_kb) AS dmv_calc
  FROM sys.dm_os_memory_brokers
  UNION ALL
  SELECT 'Free Memory (KB)' AS counter_name,
         a - b - c AS dmv_calc 
  FROM
  (SELECT SUM(virtual_address_space_committed_kb) 
          + SUM(locked_page_allocations_kb) AS a
   FROM sys.dm_os_memory_nodes) a
  CROSS JOIN
  (SELECT SUM(allocations_kb) AS b
   FROM sys.dm_os_memory_brokers) b
  CROSS JOIN
  (SELECT pages_kb AS c
   FROM sys.dm_os_memory_clerks mc
   WHERE mc.[type] = 'MEMORYCLERK_SQLBUFFERPOOL'
   AND memory_node_id < 64) c)
 SELECT opc.[object_name], 
        opc.counter_name, 
        opc.cntr_value, 
        calc.dmv_calc,
  opc.cntr_value - calc.dmv_calc AS diff 
 FROM sys.dm_os_performance_counters opc
 JOIN calc ON calc.counter_name = opc.counter_name
 WHERE opc.counter_name IN
('Target Server Memory (KB)',
 'Total Server Memory (KB)',
 'Database Cache Memory (KB)',
 'Stolen Server Memory (KB)',
 'Free Memory (KB)')
 ORDER BY opc.cntr_value DESC;

The results are pretty close  If anyone knows where the variance in database cache/stolen memory comes from (or how to get rid of it), please let me know.  I'd really like to get it dead on, even though production system values will typically be changing over time even when measured in small increments.

Here's what I got on my test system as it was ramping up...