What Is The Difference Between Checkpoint And Lazy Writer : Interview Question

Nowadays there is a very common question in interviews, “Can you please tell me the difference between Checkpoint and Lazy Writer”. I have asked this question many times but always found that differences keep on increasing as knowledge level and curiosity to know more in SQL internals increases.

Every one tells these write dirty pages in disk … blah blah .. but what else I found other than this common answer

 Read Eager Writing in SQL server

Row	Checkpoint	Lazy writer
1	Checkpoint is used by SQL engine to keep database recovery time in check	Lazy writer is used by SQL engine only to make sure there is enough memory left in sql buffer pool to accommodate new pages
2	Checkpoint always mark entry in T-log before it executes either SQL engine or manually	Lazy writer doesn’t mark any entry in T-log
3	To check the occurrence of the checkpoint , we can use below query select * from ::fn_dblog(null,null) WHERE [Operation] like ‘%CKPT’	To check the occurrence of lazy writer we can use performance monitor SQL Server Buffer Manager Lazy writes/sec
4	Checkpoint only check if the page is dirty or not	Lazy writer clears any page from memory when it satisfies all of 3 conditions. 1.      Memory is required by any object and available memory is full 2.      The cost factor of the page is zero 3.      The page is not currently referenced by any connection
5	Checkpoint is affected by two parameters 1.      Checkpoint duration: is how long the checkpoint can run for. 2.      Recovery interval: affects how often it runs.	Lazy writer is affected by 1.      Memory pressure 2.      Reference counter of the page in memory
6	Checkpoint should not be very low, it can cause increasing recovery time of database	No. of times the lazy writer is executing per second should always be low else it will show memory pressure
7	Checkpoint will run as per the defined frequency	No memory pressure, no lazy writer
8	Checkpoint tries to write as many pages as fast as possible	Lazy writer tries to write as few as necessary
9	checkpoint the process does not put the buffer page back on the free list	Lazy writer scans the buffer cache and reclaim unused pages and put it n free list
10	We can find last run entry of checkpoint in the Boot page	Lazy writer doesn’t update boot page
11	Checkpoint can be executed by the user manually or by the SQL engine	Lazy writer cant be controlled by the user
12	It keeps no. of dirty pages in memory to a minimum	It helps to reduce paging
13	Auto frequency can be controlled using recovery interval in sp_configure	Works only @ memory pressure, It uses clock algorithm for cleaning buffer cache
14	It will be automatically executed before every SQL statement which requires a consistent view of database to perform task like (Alter, backup, checkdb, snapshot …..)	It kicks pages out of memory when the reference counter of page reaches to zero
15	Command : Checkpoint	No command available
16	It comes in the picture to find min lsn whenever t-log truncates	No entry in T-log
17	Checkpoint is affected by the Database recovery model	Lazy writer doesn’t get impacted with the recovery model of the database
18	To get checkpoint entry in the error log DBCC TRACEON(3502, -1)	Not Applied
19	Members of the SYSADMIN, DB_OWNER, and DB_BACKUPOPERATOR can execute checkpoint manually	Not Applied

Reference:
Frequently Used Queries In SQL Server
Checkpoint: http://msdn.microsoft.com/en-us/library/ms188748.aspx

Lazy writer: http://sqlserverpedia.com/wiki/Memory_-_Lazy_Writer_and_Checkpoint