SQL Server Memory and paging

Vas:virtual address space, which is the largest app can request access.

Reserved Memory: To apply for a VAS address space reserve, Reserved part of the memory Page commit, then committed memory will be physical memory.

Workset is part of the Committed memory, which is the physical RAM used by the process at run time.

Page Fault:app accesses a page that is in reserved memory, but the page does not exist in Workset, and one page Fault occurs. If the target page exists on page File, then the OS will do an IO to read the target page from Disk to Workset, called hard falut, if the target page exists in committed memory, Then you only need to redirect the target page to Workset, which is called Soft Fault,soft Fault has minimal impact on performance.

Hand-painted a picture: SQL Server is pre-Reserved a large piece of memory, in use, a piece of commit commit, and committed memory in the access to physics, can understand, committed memory is SQL The physical memory used by the server to write data and read data to the physical memory page.

When memory pressure occurs, you need to focus on SQL Server's committed Memroy, Workset, and Hardfault.

The Committed memory is the reserverd memories converted to physical, which is the actual physical RAM used by the app.

The page file is a cache of files that the OS uses to temporarily store the disk file of the page in memory. If there is not enough memory, then the OS will store a portion of the page in page file, creating a write operation for disk file, and if the app needs to use the page in page file, the OS will read the page file's target page into memory. Generates a read operation on disk file. If the page file is%uage High, it means that the actual memory required by the app is larger than the committed, and the app needs to store the page frequently in disk file.

Page Fault:hard page Fault means that the OS reads the target page from the page file into physical memory, which has a large impact on the performance of the app because it generates disk IO once. Soft page Fault means that the target page exists in physical memory, but only needs to be redirected to Workset, with minimal performance impact on the app.

For a well-performing SQL Server system, hard Page Fault should be kept in a smaller number, and the less Paging the Disk, the better. There is enough memory in the system to allow the target page to reside in physical memory for a long time without the need for frequent disk paging actions.

If there is not enough memory, the OS and SQL Server do a lot of paging between the physical memory and the file cache (page swap and swap out), which results in a lot of disk IO, which makes disk IO busier, and the OS does paging action, which brings the disk IO action must run in a nuclear mindset, so the CPU time of the nuclear mentality will increase, and the user state CPU time will not be very high. In general, the high CPU time of the user state means that SQL Server is working at full capacity.

SQL Server Memory and paging