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, 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:

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:

As we all know, addition, deletion, and modificationDataFirstMemory, Which can greatly accelerateDataOperation speed; whenMemoryInDataModified, andDiskInData

Before modification, the so-called "dirty page" is generated. How is SQLServer synchronized?MemoryAndDiskOfData? The following three typesMethodIs for synchronizationMemoryAndDiskData.

LazyWrite (inertWrite):
Purpose:
1. Manage idleMemory:
A. regularly check the size of the idle buffer list. When this value is too low, it will scan the entireDataCache to release unused pages (by using the LRU algorithm)MemorySpace;
B. If you find a dirty page that has not been used for a period of time, it willWriteDiskAnd thenMemorySpace is marked as idle space;

2. Coordinate Windows and SQLServerMemory:
A. Monitoring ServerMemoryIf Windows physicalMemoryVery few, it will be released from the idle buffer listMemoryFor Windows;
B. When SQLServer is under heavy load, it will also be allocated to SQLServerMemoryMaximum server not reachedMemoryWhen the threshold value is set, the idle buffer list is added to meet the load requirements.

CheckPoint:
Purpose: The checkpoint is a time point when the checkpoint process is created. At this time point, SQLServer can confirm that all the modifications made to the submitted items have beenWriteDisk, It is a tag,
This mark isDataThe starting point of database recovery, which runs once every minute.DataLibrary, make sure thatDataThe database can be restored within one minute.
Within timeWriteLogDataIn less than 10 MB, SQLServer will not automatically initiate a checkpoint.

Trace flag 3502 records the start and end positions of checkpoints in the error log.

And LazyWriteComparison, CheckPoint does not remove dirty pages from the cache. The CheckPoint Process only ensures that dirty pages areWriteDiskAnd mark 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 manageDataPage

These operations will not wait until all pages are createdMemoryInDataWriteTo the page, instead of creating an edge with a higher priorityDataWritePage,

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