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 2
Instructor: Bob Ward

• DMVs if something is going wrong
  • dm_os_memory_nodes
    • Non-NUMA = two nodes (0 and 64 [DAC])
    • NUMA = (0, 1, 2, …)
  • dm_os_memory_clerks
  • dm_os_memory_objects
• Permon
  • SQLServer:BufferNode – per NUMA node
    • DB pages, PLE per node
  • DBCC MEMORYSTATUS
    • Away committed, Taken Away Committed – only available on this command, other columns found elsewhere
  • On 64-bit systems, ignore VirtualMemoryReserved
  • Memory clerks
    • SOSNODE – startup fixed
    • SQLSTOENG
    • SQLGENL – startup fixed
    • SQLCP – ad-hoc queries
    • SQLLOGPOOL
  • Memory standard report – add AllocatedMemory, VMCommitted, AWEAllocated to get actual memory
  • NUMA
    • Local memory favored; execution favored over “out of memory”
      • In conventional memory model set thread affninity
      • In locked memory model uses AllocateUserPhysicalPagesNuma()
      • Does not guarantee local node access
    • Taken Away – when physical node is not the SQL node
      • Not actually used at the time allocated, but reserved for local use
      • Retries a certain number of times to try to get local memory
    • Foreign Memory – when Windows doesn’t ultimately give local memory
    • Only shown in DBCC MEMORYSTATUS
  • Memory leaks
    • Out of memory error (message 701 in error log)
    • Not a Windows out-of-memory condition
    • Causes (can be SQL or can be user)
      • KB 2803065 (SQL)
      • Not closing cursors (user) (CACHESTORE_SQLCP)
      • xml_prepare_document without unprepared (user)
      • Other user-caused errors (usually heap)
        • External stored procedures
        • Unsafe CLR assemply with native calls
        • Other DLLs
      • Error log dumps
        • Look for system physical memory low and process physical memory low
        • Error 701
      • Plan cache eviction
        • Memory pressure – external clock hand
        • Cache cap limit – internal clock hand
        • Clock hand rolls through cache and decrements a cost-based counter; when zero, plan gets evicted
        • DMV to monitor external vs internal
        • DBCC FREEPROCCACHE
        • Some sp_configure changes cause eviction
      • Hard limit for all cache/user stores
        • TF 8032 uses pre-2005 SP2 behavior
        • Each cache gets a soft limit of the hard limit
        • MEMORY_CLERK_SQLLOGPOOL can be a factor; fixes to this problem available
      • RMLUtilities Suite
        • Includes ostress
        • Recommended download
      • Aggressive memory grants (workspace reservation)
        • Sorts, hash joins, index builds
        • Reserved memory for query operators
        • Typically capped at 25% of target per query
        • If grant vastly exceeds actual needs other parts of SQL become memory starved
        • Particularly bad for
          • Large data types
          • XML queries
          • Batched sorts
          • Linked servers
        • Can use Resource Governor to decrease that 25% number, but then beware of spills
      • Blocking problems
        • Large number of queries requesting grants
        • Concurrent query compilations
        • Thread-safe scalability
      • Buffer Pool Extensions (BPE)
        • Intended for SSDs, but this is not validated
      • In-Memory OLTP
        • Not evicted, cannot respond to pressure
        • Still has to honor target
      • Many SQL Server memory architecture decisions in old MS blog posts
      • See Bob’s 2013 PASS session for detailed info about SQL memory