Data Storage Internals - Pro SQL Server Internals - page 20

Let's look at what happens during data modifications. Figure 1-16 shows the initial state of the database before an
update operation. There is a memory cache, called Buffer Pool , which caches some of the data pages.
Figure 1-16. Data modification: Initial stage
Let's assume that you want to update the data row from the page (1:28992). This page is not in the Buffer pool,
and SQL Server needs to read the data page from the disk.
When the page is in memory, SQL Server updates the data row. This process includes two different steps: First
SQL Server generates a new transaction log record and synchronously writes it to the transaction log file. Next it
modifies the data row and marks the data page as modified (dirty). Figure 1-17 illustrates this point.
Figure 1-17. Data modification: Modifying data
Even though the new version of the data row is not yet saved in the data file, the transaction log record contains
enough information to reconstruct (redo) the change if needed.
Finally, at some point, SQL Server asynchronously saves the dirty data pages into the data file and a special log
record into the transaction log. This process is called a checkpoint . Figure 1-18 illustrates the checkpoint process.