Differences between NAS vs SAN vs RAID? Uses of NAS vs SAN vs RAID.

Differences between SAN vs NAS vs DAS vs RAID

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

Difference Between Lazy Writer and CheckPoint

Lazy writer and Checkpoint both are very important internal process which is used in Microsoft SQL Serve & allows to use the memory properly & minimise data loss in case of any disaster or system failures. Both serve the same purpose but there are significant differences between them.

Few differences between Lazy writer and Checkpoint are given below:

1) Trigger: Lazy Writer is triggered when the system feels memory pressure and buffer pool is running out of space but Checkpoint is triggered either log buffer is full or manually issued checkpoint by user.

Lazy Writer occurs depending on the memory pressure and resource availability but Checkpoint occurs every 1 minutes approx. or as per user request.