• Kevin Dahl - Some relevant details will be whether NUMA pass through is enabled in VMWare. This is enabled by default with more than 8 vCPUs, but for 8 vCPUs and below it is disabled unless configured manually.

    Here's why that is relevant: when SQL Server is aware of the NUMA configuration, it attempts to allocate a buffer pool of equal size on each NUMA node, the sum of which is the total buffer pool target size. However, especially in a VM, each NUMA node may not have the same number of physical cores, and may not have the same amount of physical RAM. Sizable amounts of "foreign/away pages" can result. In some cases, especially during buffer pool ramp up, a large amount of memory can end up orphaned as memory allocation requests are first granted with foreign memory and almost immediately afterward with local memory. In these cases, the local memory is used (to optimize memory latency), but the foreign memory is not released... in order to prevent repetitive cycles of allocation and release while the buffer pool is growing. While that may be a reasonable strategy, there are scenarios where the result is a large amount of orphaned, unused memory.

    Bob Dorr describes this a bit in this post.

    http://blogs.msdn.com/b/psssql/archive/2012/12/13/how-it-works-sql-server-numa-local-foreign-and-away-memory-blocks.aspx

    SQL Server 2012 SP1 CU3 contains this fix which addresses some of the scenarios which can lead to orphaned memory and memory stalls.

    http://support.microsoft.com/kb/2819662

    However, in some cases, using trace flag 8048 (to remove spinlock contention among serialized threads during memory allocation) and trace flag 8015 (to ignore NUMA configuration and manage SQL Server CPU resources and memory resources in single large pools instead of NUMA node-aligned pools) produce benefits far beyond what the SQL Server 2012 SP1 CU3 fix alone provide.

    All that said, that type of issue has been around since SQL Server 2005. It seems that the other posters in this thread have found a few similar memory management behaviors that may have been newly introduced in SQL Server 2012.