Comparison of Methods for writing SQL Server Memory Data 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, and the data in the disk is

Before modification, a so-called "dirty page" is generated. How does sqlserver synchronize data in memory and disk? 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 ages unused pages (through LRUAlgorithm) To release the memory space;
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: The checkpoint is a time point created by the checkpoint process. At this time point, sqlserver can confirm that all the modifications made to 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.
If less than 10 MB of data is written into the log within the time 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 page in the cache
Clean pages.

Eagerwrite:
Usually occurs in BCP, select into, writetext, updatetext, bulk insert, etc. To speed up these operations, eagerwrite will manage the data page

These operations will not write data in the memory to the page after all the pages are created. Instead, the data in the cache will be written to the page while being created at a higher priority,

To ensure that the buffer pool has enough space to complete these operations.