I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Troubleshooting SQL Server Memory, Part 1
Instructor: Bob Ward

Primary memory consumers in SQL Server

• • Buffer pool (BPool) – number 1 consumer
  • Plan cache – traditionally number 2 consumer
  • Other (log pool, token perm)
  • Hosted memory (CLR)
  • Fixed memory (fundamental operations of SQL Server, such as connections)
  • In-Memory OLTP
  • Columnstore
• As of 2012, max server memory = ALL server memory, not just the BPool
• Locked pages – must be enabled on the service account at the OS level group policy
• max server memory: 0 = min( 95% of physical RAM, 50% available RAM )
• min server memory
• Not coordinated between instances
• Paging – only the BPool is pagable to disk
• Windows paging
  • Working set trim operations
• Three memory models
  • Conventional
  • Locked pages
  • Large pages
• Locked pages memory model
  • Locked pages in memory privilege
  • Only Standard and Enterprise Editions
  • Memory is no longer part of the process’ working set
  • Recommended to use this feature especially if it is a dedicated box, including VM
  • Also take a hard look at max server memory
  • SQL will trim its own memory as requested by the OS down to min server memory
  • Recommend don’t set min unless running multiple instances
  • Private bytes include locked memory
• DMVs
  • dm_os_sys_info
  • dm_os_sys_memory (Win counters)
  • dm_os_process_memory (very valuable for seeing what SQL thinks it has)
    • physical_memory_in_use_kb
  • Useful perfmon counters
    • Target server memory (KB)
    • Total server memory (KB)
  • Private bytes include process-based memory (stack space, etc) so will be somewhat higher than total server memory
    • If it does not stay at about a fixed amount over total server memory, there is probably a memory leak (most likely in a DLL)
  • Memory consumption standard report in SSMS
  • Buffer pool disfavoring – pages from large scans get discarded earlier
  • dm_os_buffer_descriptors – advanced diagnostics
    • group by to see the number of hashed buffer pages
  • Plan cache troubleshooting
    • Formula to determine cache cap size
    • Watch perfmon compilations/sec – may indicate a high rate of eviction
    • dm_exec_cached_plans
    • Bloat can be caused by non-parameterized plans
    • TF 8032 – increase size of all caches (uses pre-2012 formula) – use with caution
  • Known issues with memory
    • NUMA with large memory (256+ GB), see KB article
    • Large memory model (TF 834)
    • Standard Edition size limits only apply BPool
    • Known memory leak bugs
    • Read recommended articles at end of resources