Checkpoint And Lazy Writer
Now a days there is a very common question in database 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 are keep on increasing as knowledge level and curiosity to know more in sql internals increases.
Every one tells these write dirty pages in disk … bla bla .. but what else I found other than this common answer
| 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 | Check point 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 occurrence of checkpoint , we can use below query select * from ::fn_dblog(null,null) WHERE [Operation] like ‘%CKPT’ | To check occurrence of lazy writer we can use performance monitor SQL Server Buffer Manager Lazy writes/sec |
| 4 | Checkpoint only check if 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. Cost factor of page is zero
3. Page is not currently reference 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 page in memory
|
| 6 | Check point should not be very low , it can cause increasing recovery time of database | No. of times lazy writer is executing per second should always be low else it will show memory pressure |
| 7 | Checkpoint will run as per 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 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 Boot page | Lazy writer doesn’t update boot page |
| 11 | Checkpoint can be executed by user manually or by SQL engine | Lazy writer cant be controlled by user |
| 12 | It keeps no. of dirty pages in memory to 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 consistent view of database to perform task like (Alter, backup, checkdb, snapshot …..) | It kicks pages out of memory when reference counter of page reaches to zero |
| 15 | Command : Checkpoint | No command available |
| 16 | It comes in picture to find min lsn whenever t-log truncates | No entry in T-log |
| 17 | Checkpoint is affected by Database recovery model | Lazy writer doesn’t get impacted with recovery model of database |
| 18 | To get checkpoint entry in error log DBCC TRACEON(3502, -1) | Not Applied |
| 19 | Members of the SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR can execute checkpoint maually | Not Applied |
Thanks, couldn't find more detailed comparison.
ReplyDelete