Memory classification for SQL Server

First Class,

According to the method of application:

Commit Type

It refers to the amount of space that the reserve applies for a chunk before it is submitted by commit. This way the application to the space can be enabled AWE!

Stolen type

correspond to commit! It does not use the reserve just to apply directly. It cannot apply for AWE to expand the memory of the exhibit.

Type II,

Depending on the size of the application space:

Single page allocation (buffer pool) Type:

If you want to apply for a large amount of space 8KB SQL Server will be generous to the 8KB (a data page)!

Multiple page allocation (MemToLeave) type:

If the requested page memory is greater than 8KB, it requests more SQL Server to give it how much!

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

Summarize:

1, Database Cache because it is a data page, so it is stored in the single page allocation (buffer pool)

2, connection it is stored in which it did not count itself, to see the network package size of the face,

The network package size is 4KB by default so connection is stored by default in single page allocation (buffer pool), unfortunately

The size of the network package size can be adjusted by sp_configure. So connection may also be stored in multiple page allocation

3, general (lock data structure, transaction context, table index Metadata) They are also dynamic, if they are relatively small stored in single page allocation

If you write the statements longer, they are stored in multiple page allocation.

4. Third-party code, SQL Server does not know how long it is, so it is stored in mulitple page allocation.

5, thread, because each thread's data structure size is minimum 512KB, so it is stored in mulitple page allocation.

Here's how the memory goes,

(The following is an example of 32-bit system 4G memory, 256 threads)

1. Multiple page allocation:

It's called MemToLeave because SQL Server reserved the address at startup and reserved

That's how it came about. + THREADS*0.5MB; Threads refers to the maximum number of threads, which can be viewed through sp_configure.

The multiple page allocation = 384MB is calculated according to the above expression, and when SQL Server mulitple page allocation

When you reach 384MB, SQL Server will no longer be able to request this kind of memory.

2. Single page allocation:

The size of the multiple page allocation is determined, and the size of the single page allocation is known.

Single page allocation = (2G-384MB) = 1664MB, which is the maximum value of the single page allocation

SQL Server was just started with 1664MB, when SQL Server usage reached 1664MB or max server memory

parameter, SQL Server can no longer request this memory, and the value of single page allocation is limited.

Then

If the memory is greater than 4G, it is said that the user too much memory is greater than 2G, and open AWE, the extra memory can also be used.

It said that AWE's memory can only be used by the database cache in Reserve\commit way, and the other memory is not used, which

Sample Singel page allocation stolen can be used up to 1664MB, the database cache can be extended out of awe space.

Then

If you have 32-bit SQL Server on a 64 machine, because the 64-bit operating system does not use the address space of the 32-bit application, ah sql

Server up to 4G available, so single page allocation = (4g-384) = 3664MB;

SQL Server uses the least recently used algorithm to retire pages in memory.

Memory classification for SQL Server