Sql-server – Did the Change Tracking internals change from SQL Server 2008 to 2012

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread stacks, etc.

You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to fully control the memory used by an instance of SQL Server. If you want the whole instance (not just buffer pool, plan caches, and CLR) to use no more than 64GB, you should set max server memory to something lower.

Some potential ideas for tracking this down (I will normalize everything to MB):

• performance counters

  See if anything jumps out here as excessively large:

  SELECT counter_name, instance_name, mb = cntr_value/1024.0
    FROM sys.dm_os_performance_counters 
    WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
    OR (instance_name = N'' AND counter_name IN 
         (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', 
          N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', 
          N'Log Pool Memory (KB)', N'Free Memory (KB)')
    ) ORDER BY mb DESC;
  

• top 20 clerks

  You've already done this, but for completeness:

  SELECT TOP (21) [type] = COALESCE([type],'Total'), 
    mb = SUM(pages_kb/1024.0)
  FROM sys.dm_os_memory_clerks
  GROUP BY GROUPING SETS((type),())
  ORDER BY mb DESC;
  

• thread stack size

  First, make sure this is zero, and not some custom number (if it is not 0, find out why, and fix it):

  SELECT value_in_use
    FROM sys.configurations 
    WHERE name = N'max worker threads';
  

  But you can also see how much memory is being taken up by thread stacks using:

  SELECT stack_size_in_bytes/1024.0/1024 
    FROM sys.dm_os_sys_info;
  

• 3rd party modules loaded

  SELECT base_address, description, name
    FROM sys.dm_os_loaded_modules 
    WHERE company NOT LIKE N'Microsoft%';
  
  -- you can probably trace down memory usage using the base_address
  

• memory-related DMVs

  You may also be able to spot something out of the ordinary looking at these DMVs:

  SELECT * FROM sys.dm_os_sys_memory;
  SELECT * FROM sys.dm_os_memory_nodes WHERE memory_node_id <> 64;
  

This article was written before SQL Server 2012, so some column names and calculations may have to be adjusted, but may give some other avenues to try as well:

• Troubleshooting SQL Server Memory

Some good background in another article on that site too:

• SQL Server 2012 Memory

Some good info about the types of things that use memory outside of max server memory (but no good data about how to collect the actual usage):

• MTL Consumers

_{Community Wiki answer originally left as a comment by the question author}

Resolution: I unchecked “Automatically set processor affinity mask for all processors” and checked the individual CPUs. I had also already changed the MaxDOP = 0 (it was even faster than 1 or 3).

Results are blazing fast! So the bug must be that the "Automatically set processor affinity mask for all processors" box does not actually use all the CPUs, it must only see one of them. Manually selecting all the CPUs is required. This was true for both the 2008 R2 and 2012 servers.