SQL Server 2008 Memory Performance monitoring

Memory-related concepts

The following are for Windows 32-bit system environments where 64-bit is not described below.

User mode and kernel mode (username mode& kernel mode)

To prevent user programs from accessing and tampering with critical parts of the operating system, Windows uses 2 processor access modes: User mode and kernel mode. As the name suggests, kernel mode is for operating system core code and basic drive, user mode to user application. In kernel mode, the program can access all the memory and hardware and use all the processor instructions. The operating System program has higher privileges than the user program, making it possible for the system designer to ensure that the user program does not accidentally destroy the system's stability.

Physical memory (physical Memory)

That is, the size of the actual memory purchased, the capacity on the memory strip. The CPU address line can be addressed directly to the size of the memory space, on the 32-bit operating system platform, the CPU's maximum addressing space is 4GB, can also support the maximum 4G of physical memory space. On a 32-bit operating system, even if you buy 64G of memory, you can only say that you have 4GB of physical memory space

Virtual Memory (Memory)

If the computer lacks random storage memory required to run programs or operations, Windows uses virtual memory for compensation. Virtual memory combines the computer's RAM with the temporary space on the hard disk, and when the RAM is low, the virtual memory moves the data from RAM to a space called the paging file, and moving the data into and out of the paging file frees up RAM to complete the work.

Virtual address space (for short VAS)

In Windows systems, any process is given its own virtual address space, which covers a considerable range of virtual address space for 32-bit systems from 0X00000000~0XFFFFFFFF (4GB). Windows uses a paging mechanism that divides the 4G address space into a fixed sized page and maps each page in the virtual address to physical memory.

In the default case, the low 2G in the virtual address space, that is, the 0X0000000~0X7FFFFFFFF is the user address space, and the 4G virtual address space in the high 2G that 0X8000000~0XFFFFFFFF is allocated to kernel mode. In fact, the user process has only 2GB of virtual address space.

Virtual Memory Manager (VM Memory Manager)

The virtual Memory Manager is responsible for address mapping of virtual address space and physical memory, and if insufficient memory is needed, use the page file file to keep temporary data, or virtual memory, and use the page table entry (PTE) to track each address mapping relationship.

So far, 64G of memory 60G can not access, it is not a waste, then how to do?

/3GB and Increaseuserva

By/3GB, the kernel mode can be reduced to occupy the address space, thereby increasing the address space of the SQL Server process. By default, user mode and kernel mode each occupy 2G of addressing space, and the 3G option allows SQL Server to obtain more than 1G of virtual address space.

/3GB Switch Usage:

Modify the paragraphs in the Boot.ini file to:

or use the bootcfg command

You can run the bcdedit command in Windows Server 2008 and adjust it.

Physical Addresses extension PAE (Physical address Extension)

Physical Address Extension (PAE) is an extension of 32-bit Intel CPUs that can support up to 64G of physical memory on 32-bit systems, that is, more than 4GB of physical memory allows more physical memory to be mapped to the application's virtual address space.

Using the method, modify the paragraph in the Boot.ini file: