What is Dirty pages in SQL Server? Why SQL Server uses dirty pages?

A dirty page is a term used in Microsoft SQL Server. A dirty page is a data page that has been modified in the buffer cache but still not transferred to disk.

When we execute any update query, the system updates the change in memory first and the corresponding data page has been marked as dirty.

SQL Server engine runs a process to write all such dirty pages back to disk.

Flushing is the process that writes all dirty pages back to disk. Several events can trigger the Flushing process. 

A few are as below:

1. When the SQL Server engine needs to free up memory for other processes

2. When a checkpoint occurs

3. When a transaction commits

Why SQL Server uses dirty pages?

SQL Server uses dirty pages because of the performance of the system.

Data writing to disk is a time-consuming process.

By reducing the number of disk writes, SQL Server can improve performance.

Besides performance and reliability implications, dirty pages may also affect certain SQL server features. For Example, the query optimizer uses statistics in the database to generate query plans if a dirty page contains statistics.

Administrators can use several tools and techniques to manage dirty pages in a SQL Server instance. 

The SQL Server dynamic management views (DMVs) provide information about the current state of buffers, including the number of dirty pages and the rate at which they are flushed. 

By monitoring these metrics, administrators can identify potential performance issues and take corrective action before they become critical.