Checkpoint

• Checkpoint occur on Recovery time interval, Backup/Detach Command & on the execution of DDL command.
• Checkpoint writes only dirty pages to the disk. Checkpoint does not release any memory.
• The checkpoint is responsible for DB recovery point.
• Checkpoint always mark entry in T-log before it executes either SQL engine or manually
• Checkpoint occurrence can be monitored using performance monitor “SQL Server BUFFER Manager Checkpoint/sec”.
• You need SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR rights to execute checkpoint manually

Lazy Writer

• Lazy Writer release buffer pool memory when memory pressure occurs to ensure enough FREE memory.
• Lazy Writer looks for LRU, least recently used (“COLD” = least recently read or WRITTEN, not accessed in recent time) pages in the buffer pool and releases the memory taken by them. Lazy writer releases both dirty and clean pages. Clean pages can be released without writing to disk and dirty pages release after written to the disk.
• Lazy Writer is only responsible for managing free buffer SPACE. The lazy writer does not affect or manage database recovery.
• The lazy writer doesn’t mark any entry in T-log.
• Lazy writer occurrence can be monitored using performance monitor “SQL Server Buffer Manager Lazy writes/sec”.
• SQL server executes its own. User cannot run it manually