Detailed explanation of SQL Server's memory management architecture

WindowsSQL Server 2.12

Windows

Windows VMM

11

SQL Server

SQL Server SQL Server

I/OSQL Server SQL Server SQL Server

• I/O

2.1

SQL Server 64 SQL Server x64 SQL Server 128 GB IA64 7 TBSQL Server 64 2012.x IA11

2.2 AWE

AWE AWE LPIM SQL Server RAM 32 64 AWE AWE Windows AWE API “”SeLockMemoryPrivilege AWE API RAM

LPIM MB 2147483647 MB

LPIMSQL Server 17890

SQL Server 2012 11.x

3.1

SQL Server 2012 11.x CLR “” MB MB SQL Server

SQL Server 2012 11.x SQL Server MB KB KB MB 8 KB SQL Server

KB KB SQL Server MB KB MB KB

3.2memory_to_reserve

SQL Server SQL Server VAS MPACLR SQL Server DWA “”“”

memory_to_reserveSQL Server 256 MB

“” 8 KB memory_to_reserve

SQL Server SQL Server Microsoft Windows API

SQL Server OS SQL Server SQL Server SQL Server ;

SQL Server CLR1sys.dm_os_memory_clerks

CLR SQL Server 2012 11.x max_server_memory

SELECT  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,    page_fault_count AS sql_page_fault_count,    memory_utilization_percentage AS sql_memory_utilization_percentage,    process_physical_memory_low AS sql_process_physical_memory_low,    process_virtual_memory_low AS sql_process_virtual_memory_lowFROM sys.dm_os_process_memory;

4.1

CLR.dll OLE DB Transact-SQL SQL Server DLL MB

SQL Server SQL Server

SQL Server SQL Server MB ;

SQL Server SQL Server SQL Server SQL Server SQL Server

SQL Server I/O I/O SQL Server I/O I/O

5.1

8 KB 8 KB

SQL Server SQL Server sys.dm_os_sys_infocommitted_target_kbcommitted_kb

SQL Server 8 KB

SQL Server

• 32
• I/O SQL Server SQLOS

5.2

• NUMA NUMA NUMA
• 64 Windows
• SQL Server

5.3 I/O

I/O

• I/O I/O
• I/O I/O I/O SQL Server I/O CPU SQL Server OLTP I/O SQL Server
• I/O - I/O SQL Server I/O

5.4 I/O

15 I/O SQL Server I/O 833 SQL Server I/O ; I/O SQL Server I/O SQL Server SQL Server I/O

5.5 I/O

I/O I/O I/O I/O
I/O SQL Server

• SQL Server I/O

I/O I/O I/O /“” I/O I/O I/O

SQL Server NUMA NUMA L3 NUMA

SQL Server NUMA

SQLServer,SQLServer