Check point and Lazy writer in SQL Server

 Checkpoint:

The checkpoint process also scans the buffer cache periodically and writes any dirty data pages for a particular database to disk.

The Checkpoint process doesn't actually remove pages from the buffer pool. All it does in the buffer pool is mark dirty pages as clean, so the page is still actually in the buffer pool

          Checkpoint is the process wherein Dirty Buffer (database pages which have

          been modified by a DML operation) back to the disk. It is not only mark the

          dirty pages but also flush dirty pages to Disk.

 Desc:

1. Checkpoint occurs on database level.
2.  select  * from ::fn_dblog(null,null)  WHERE [Operation] like ‘%CKPT’
3. select top 10  [Operation],[checkpoint begin],[checkpoint end] from ::fn_dblog(null,null)  WHERE [Operation] in(‘LOP_BEGIN_CKPT’, ‘LOP_END_CKPT’,’LOP_XACT_CKPT’)
4. Also enabling trace flag will provide information on error log when checkpoint started at what database.
5. DBCC TRACEON(3502, -1)
6.  Checkpoint impact the performance (very low IO) for heavy system, so we can even disable automatic checkpoint —-Never do this, using trace flag 3505

Lazy Writer:

The lazy writer thread sleeps for a specific interval of time, and when it wakes up, it examines the size of the free buffer list.

The purpose of Lazy Writer is to maintain some free buffers in the SQL Server Buffer Pool. Lazy writer runs periodically and check which buffers can be flushed and returned to the free pool. So even SQL Server is not under memory pressure, it will also work regularly.

Desc:

1. Lazy writer is on the server  to check when lazy writer occurs use
2. SQL Server Buffer Manager Lazy writes/sec