SQL Server Storage Engine--memory

SQL Server Storage Engine Memory Chapter:

(1) SQL SERVER memory structure

SQL SERVER Memory structure diagram

The SQL SERVER memory space can be divided into two main parts:

　　

(1.1) Executable (executable code)

Mainly includes some EXE and DLL files in the SQL SERVER instance

(0) SQL Server engines (SQL Server engine), the main program of SQL Server database;

(1) Service-side network library (SERVER net-library) for communicating with client network libraries, handing TDS packets to open Data services, and information about SQL Server access in SQL Server architecture ；

(2) Open Data Services, which restores the SQL statements in the TDS package to the relational engine, and describes the content of the relational engine in the SQL SERVER relational engine;

(3) Distributed queries (distributed QUERIES) to support access to data from heterogeneous data sources (also from SQL Server to SQL Server), where OLE DB PROVIDER accessing heterogeneous data sources is loaded;

(4) Extended stored procedures (EXTENDED STORED procedures), in subsequent releases will be used to completely replace the extended stored procedures with CLR integration;

(5) OLE Automation (OLE AUTOMATION), which is used by SQL Server to reference OLE Automation objects, is actually an extended stored procedure, listed solely because of its functional specificity;

(1.2), buffer pool

(0) system level data structure (system-level data structures), the global information on the data instance, including the database description, the lock, the object being referenced and the database, etc.

(1) connection context (CONNECTION), a bit like the concept of a session in a Web application, contains the current database connection and state, the parameters of the stored procedure, the current position of the cursor, the referenced table, etc.

(2) procedure cache (PROCEDURE cache), execution plan for all stored procedures or SQL statements, and if the SQL statement has an appropriate execution plan, use the execution plan directly, without having to compile the SQL statement again. The cache hit RATIO in the performance counter plan cache is referred to here.

(3) data cache for database data or index page read and write, performance counter cache hit ratio in Buffer Manager (buffer cache hits RATIO) is referred to here. In the case of physical memory adequacy, this value is usually higher than 90% or higher, only a few data pages need to be physically read, if less than 90% may be required to expand the physical memory;

(4) Log cache, for the database log page read and write, each DML will generate the corresponding redo log operations here, if the transaction is rolled back, the corresponding reverse redo log is generated according to the redo log;

Only the size of the process cache, the data cache, and the log cache are dynamically managed by SQL Server based on performance requirements, while other memory spaces are changing as the user requests originate.

(2) SQL Server memory management

　　(2.1) Process address space

Process address spatial structure diagram

(0) VAS (virtual address space): The process address spaces, or the name of the space, is the memory addressing space, 32-bit CPU and operating system (hereinafter referred to as 32 system), addressing space of 2 32, that is, 4G;

(1) 3gb:32-bit 4G address space on the Windows system, the application only 2G access (that is, the user simulation address space), the remaining 2G left to the operating system itself (that is, the kernel simulation address space), by opening the 3GB option, you can have the application addressing the 3G addresses space;

(2) PAE (physical addressing Extensions): Physical Address Extension, raising the addressing space of a 32-bit Windows system to 2 36 parties, or 64g,awe (address Windows Extension) is an address window extension or API, applications can use PAE's address space through AWE (first open the operating system's PAE, and then implement awe of the application);

(2.2) SQL Server memory management

(0) The buffer pool of SQL Server is unified with 8k as the memory block size, and large memory blocks larger than 8k are independently managed and distributed directly from the buffer pool through the operating system;

(1) AWE memory can only be used for data caching, and other memory components cannot use AWE memory;

(2) Lock the memory page (lock pages in memory) and assign this operating system policy to the user who started the SQL Server service to prevent SQL Server from switching from physical memory to page file. When you turn on AWE, you must explicitly assign the lock pages in memory permission for the SQL Server service to start users other than the local system;

(3) SQL SERVER continues to acquire physical memory as user connections and requests increase until it reaches the smaller of the target memory and the maximum server memory (max server memories). If the operating system on the SQL Server server does not have free memory, or if other applications have memory pressure present, SQL Server adjusts its target memory size and frees up physical memory, but does not release to less than the minimum server memory (min server memories) ; If there is no additional memory pressure, the memory that SQL Server acquires during busy time is not actively freed when idle.

--------------------------------------------------------------------------------------

Translated from: http://qianzhang.blog.51cto.com/317608/1217346

--------------------------------------------------------------------------------------

SQL Server Storage Engine--memory