Sql-server – Why is an entire index that is infrequently used stored in the buffer pool

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

When SQL Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer pool, correct?

No the memory is from inside the buffer pool. Generally memory required for such process would 'not be much' and this could be easily satisfied by buffer pool. Also note SQL Server might not grant all memory requested by the query in one go. If query requests memory which is of considerable amount SQL Server would grant minimum just to make sure query starts running.

The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Buffer pool is always there starting from when plan is created to when query executes. Buffer pool in SQL Server 2008 R2 is sole one allocating memory for any request <= 8KB of memory page.

If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type. My thought is set MAX Memory at 72 GB. Your thoughts?

No, reducing max server memory would be of help here. I would suggest you to look at query and the 'statistics optimizer is looking when it is preparing the query plan'. You have enough amount of RAM. Sometimes when you run a query with outdated stats it requests large amount of memory and ofcourse SQL Server is not going to grant all of it. It would provide minimum amount so that query at least starts running.

When SQL server creates a compiled plan, it calculates two memory grant parameters called "required memory" and "additional memory". The required memory is minimum memory SQL Server can give for sorting and hash operation and additional is what can be required by query to store temporary data which comes with huge sorts or spills. If Query start with minimum memory and requests more but SQL Server is not able to provide it all in on go query waits with wait type 'resource semaphore'. This does not means memory is less it means the statistics was outdated which forced SQL Server to create bad plan thus requesting more memory.

I suggest you read Understanding Query Memory Grants