Investigating OOMs on SQL Server 2016 SP1 CU4 - Part I

Today OOMs were reported on a physical server with 1.5TB of RAM, running SQL Server 2016 SP1 CU4.  I started to poke around a bit... pretty sure we'll need to set up perfmon and maybe a SQL agent job to capture numbers every 5 minutes.  Capturing the memory clerk numbers seems like the place to be.



Here's the SQL if you want to play along at home. But I'm too sleepy to do anything with it tonight.

;WITH clerk_kb 
     AS (SELECT [type],
                --roll up 'ACRUserStore%' & 'SecCtxtACRUserStore' numbers
                CASE WHEN [name] LIKE 'ACRUserStore%' THEN 'ACRUserStore' 
                     WHEN [name] LIKE 'SecCtxtACRUserStore%' THEN 'SecCtxtACRUserStore' 
                ELSE [name] END clerk_name, 
                memory_node_id, pages_kb
        FROM sys.dm_os_memory_clerks)
SELECT GETDATE() AS capture_tm, clerk_name, memory_node_id, SUM(pages_kb) pages_kb
FROM clerk_kb
--keep the two clerks per database - dbname and {objperm + dbname} - out of results 
WHERE NOT EXISTS (SELECT 1 FROM sys.databases sdb 
                  WHERE PATINDEX('%' + sdb.[name] + '%', clerk_name) > 0)
GROUP BY clerk_name, memory_node_id
HAVING SUM(pages_kb) > 40000
ORDER BY clerk_name, memory_node_id;