Comparison of Methods for writing data from SQLServer memory to Disks

As we all know, adds, deletes, and modifies data first in the memory, which can greatly speed up data operations. When the data in the memory is modified, when the data on the disk has not been modified, the so-called dirty pages are generated. How does SQLServer synchronize the memory and disk data? The following three methods are used to synchronize memory and disk data.

As we all know, adds, deletes, and modifies data first in the memory, which can greatly speed up data operations. When the data in the memory is modified, when the data on the disk has not been modified, the so-called dirty pages are generated. How does SQLServer synchronize the memory and disk data? The following three methods are used to synchronize memory and disk data.

As we all know, adds, deletes, and modifies data first in the memory, which can greatly speed up data operations. When the data in the memory is modified, when the data on the disk has not been modified, the U.S. server generates a so-called "dirty page". How does SQLServer synchronize the memory and disk data? The following three methods are generated to synchronize memory and disk data:

LazyWrite (inert writer ):

Purpose:

1. Manage SQLServer idle memory:

A. regularly check the size of the idle buffer list. When this value is too low, It scans the entire data cache and releases the memory space by aging unused pages (through the LRU algorithm;

B. If you find a dirty page that has not been used for a period of time, it will write it to the disk and mark the memory space on this page as idle space;

2. Coordinate Windows and SQLServer memory:

A. Monitor the server memory. If the Windows physical memory is small, it will release the memory from the idle buffer list to Windows;

B. When the SQLServer load is heavy, it will also increase the idle buffer list size when the memory allocated to SQLServer does not reach the maximum Server Memory threshold to meet the load requirements.

CheckPoint:

Purpose:

A checkpoint is a time point created by the checkpoint process. It is free of Record Filing space. At this time point, SQLServer can confirm that all the modifications made by the submitted items have been written to the disk. It is a tag, this mark is the starting point for database recovery and runs every minute. This ensures that the database can be restored within one minute when the database is started; however, if less than 10 MB of log data is written during this period, SQLServer will not automatically initiate a checkpoint. Trace flag 3502 records the start and end positions of checkpoints in the error log.

Compared with LazyWrite, CheckPoint does not remove dirty pages from the cache. The CheckPoint Process only ensures that dirty pages are written to the disk and marks the cached page as a clean page in the page header.

EagerWrite:

Generally, operations such as BCP, select into, WRITETEXT, UPDATETEXT, and bulk insert occur. to speed up these operations, EagerWrite manages the creation and allocation of data pages, these operations do not write data in the memory to the page until all the pages are created, but write the cached data to the page while creating the pages with a higher priority, to ensure that the buffer pool has enough space to complete these operations.

, US space