How FlushCache works: Because the hard disk is slow and the memory is fast, SQL Server uses Cache to store commonly used data in the memory. to or read from a SQL Server database, the buffer manager copies it into memory. Buffer Cache will allocate memory to it and save as much data as possible. When the Buffer Cache fills up, older and less used data will also be cleared so that new data can be placed in the Cache.
The data stored in the Cache can be divided into clean pages and dirty pages: dirty pages are pages that have changed since the last time they were bot dataset written to the disk; clean pages are unchanged pages, and the data in them are still the same as those on the disk. The content is the same. SQL Server will periodically issue checkpoints, which will write dirty pages to disk.
Large memory challenges: As the memory capacity increases, the implementation of this checkpoint takes more and more time because it needs to scan the entire Buffer Cache. Taking a 12TB memory as an example, even if there is no dirty page, It takes 12 seconds to scan. SQL Server 2016 uses Dirty Page Magager (DPM) to solve this problem, but still retains some scenarios where FlushCache continues to be used (Table 1 below) Research-on-the-Causes-of-IO-Delay-in-SQL-Server-01 In our situation, we happened to encounter.