INF: When Dirty Cache Pages Are Flushed to Disk

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

To improve performance, SQL Server maintains a cache of the previously read and/or modified ("dirty") pages. Periodically, these pages are physically written to the disk, ensuring that the changes to any rows are reflected in user databases.

MORE INFORMATION

SQL Server will flush pages from the cache to the disk under the following conditions:

1. When a checkpoint is issued.

   There are five conditions that can cause a checkpoint to be issued:

   a. The database owner or the system administrator (SA) can issue a

         CHECKPOINT command at any time.
   

   b. When a DUMP DATABASE or DUMP TRANSACTION command is issued, the

         first step of the DUMP process is to do a checkpoint in that
         database.
   

   c. A checkpoint is issued in each database after the recovery

         process has finished recovering that database. This feature can
         be disabled by using the sp_dboption system procedure with the
         "no chkpt on recovery" option set to true.
   

   d. A checkpoint will automatically be issued by the checkpoint

         checking process when it determines that enough transactions
         have occurred since the last checkpoint. Although the checkpoint
         checking process wakes up about once per minute to see how much
         activity has taken place, the frequency with which it actually
         does a checkpoint is determined by the number of transactions
         and the value that has been set for the recovery interval. Note
         that if the "trunc. log on chkpt." option has been set using
         sp_dboption, a checkpoint will occur every time the checkpoint
         checking process wakes up (about once per minute).
   

   e. A checkpoint is issued when the SA issues the SHUTDOWN command.

2. When a new page needs to be brought into the cache.

   When SQL Server determines that a page is needed that is not currently in the cache, it looks for a free buffer in the cache page chain. When it has finished going through the chain, the page from the oldest buffer is flushed to disk.

3. At the end of a fast bulk copy or SELECT INTO.

   When the fast BCP (non-logged version) is used, or when a table is created using SELECT INTO, the newly-inserted rows are not logged. Also, many or all of them may still be in the cache. To lessen the chance of losing data in the event that SQL Server is stopped without a checkpoint, all dirty pages are flushed to disk when the BCP or SELECT INTO finishes.

4. Log pages are flushed when a transaction ends.

   When a transaction ends, either with a COMMIT or ABORT, the log pages are flushed to disk.

5. When a page is split, the newly allocated page is immediately flushed to disk.

6. LOAD DATABASE writes all pages to disk.

   When a database is being loaded with the LOAD DATABASE command, all pages in the dump are written directly to disk. In addition, any pages in the database that are not in the dump are also initialized and flushed to disk. For example, if you are loading a dump from a 4 MB database into a 10 MB database, the remaining 6 MB of pages will all be initialized and written to disk.

7. LAZY WRITER process flushes dirty pages.

   A new system process, Lazywriter, has been added to Microsoft SQL Server version 4.21. The Lazywriter process automatically starts flushing buffers when the number of available free buffers falls below a certain threshold, and it stops flushing buffers when this number goes ~5-6% above the threshold. This threshold value is specified as a percentage of the total number of buffers in the buffer cache. The default threshold is set to 3% of the buffers in the data cache. For more information refer to the Microsoft SQL Server Version 4.21 release notes (\sql\install\readme.txt).