SQL server has 2 Memory Manager. Buffer Pool and Memory manager. Buffer Pool manage the single page , the primary consumer for the single pages are Data Buffer and procedure cache. There are other components not data buffer also use single page, it is call stolen page. When we configure SQL server MAX Memory, we actually only setup the the Max Size of the Buffer Pool ( Single page) only. When SQL Server needs multi pages ( page more than 8K), it switch to new implement memory manger. components outside of single pages are CLR, MPA, DWA and TS.
SQL Server 2012
Only one memory manager that mange all pages, including Buffer Pool. CLR does not directly manage by memory manager but track through the VAS allocator. components outside of memory manager are DWA and TS.
VAS size in 64-bitx64 has 8TB . x86 has 4GB. WOW64 has 4GB user space memory, kernel space memory live outside of woo64.
Christian Bolton has VAS Summary query on his blog post SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog:
TS – Thread stackBelow the T-SQL can use to check the current work threads.
In 64-bit environment :
- CPU ( Logical processes ) < 4 : Max worker threads is 512.
- CPU (Logical processes ) > 4 : Max worker threads is 512 + ( number of CPU –4 ) * 16
- Each worker thread require 2MB memory. There the Thread stack memory size is 2MB * number of worker threads.
- It is important to reserve the enough memory for the max worker threads . In the example above, the max worker thread is 512 . It will need 1024MB memory for TS.
DWA – Direct Windows AllocationThis is the access through extend store procedure, OLE automation ( sp_OA ) , Link server etc.
sys.dm_os_load_modules can show the DLL that load within SQL Server.
- External Physical memory pressure: Windows Memory manager
- Internal Logical memory pressure: Buffer Pool
- Internal Virtual memory pressure: VAS
Internal resource pool is reserve for SQL Server internal only. We cannot alter it.
Buffer Pool ( Data )
- Biggest consumer for the Memory.
- Each Hardware NUMA node has its own memory. Hence, it has its own buffer pool.
- Each Hardware NUMA node has its own lazy writer thread to perform the buffer pool trimming process to write the dirty pages to disk base on LRU ( Last Recent Used) Algorism . Lazy writer would only start the trimming it has low free page ( Internal logical pressure ) or Resource monitor notification ( External physical memory pressure ).
- Lazy writer always check 16 buffers at a time.
Below query shows what is in buffer pool for the current database.
Procedure Cacheusually the 2nd biggest consumer beside buffer in the memory .
Below is the query shows each CACH Objects size for SQL 2008 . The 2012 would have some slightly difference.
'CACHESTORE_SQLCP'', ''CACHESTORE_OBJCP'', ''CACHESTORE_PHDR'',''CACHESTORE_XPROC'')')
LPIM ( Lock pages in memory)
- Generally, it is not required.
- To set up, the sql server account needs to be grand “Lock pages in memory” privilege.
Put it together :Here is the query that shows the various memory status within the SQL server.
Memory Manager surface area changes in SQL Server 2012: http://blogs.msdn.com/b/sqlosteam/archive/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012.aspx
Memory Manager Configuration changes in SQL Server 2012: http://blogs.msdn.com/b/sqlosteam/archive/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012.aspx
An in-depth look at SQL Server Memory–Part 1: http://blogs.msdn.com/b/sqljourney/archive/2012/12/17/an-in-depth-look-at-sql-server-memory-part-1.aspx
An in-depth look at SQL Server Memory–Part 2: http://blogs.msdn.com/b/sqljourney/archive/2013/02/13/an-in-depth-look-at-sql-server-memory-part-2.aspx
An in-depth look at SQL Server Memory–Part 3: http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspx