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

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

22 comments:

  1. Really awesome collectoin. thanks a lot for knowledge sharing. I really understood the exact difference with your post.

    ReplyDelete
    Replies
    1. Thanks Ravinder, I will try to come up with more

      Delete
  2. From long time I am looking such a great post. Now I am clear about checkpoint and lazy writer

    ReplyDelete
  3. Thank u so very much
    Now m clear wid d differenc
    Thank u once again

    ReplyDelete
  4. Good article!
    MSDN says "After an execution plan is generated, it stays in the procedure cache. SQL Server 2005 ages old, unused plans out of the cache only when space is required."

    This means that "Lazy Writer" wakes up but only goes into action when there is a memory pressure. "Lazy Writer" works on Plan Cache (procedure cache) to rank the usefulness of each object, which is another major tasks it performs.

    ReplyDelete
    Replies
    1. Thanks Sarjen , Appericiate your hawk eye on my blog. looking for more

      Delete
  5. Thank for sharing this Saurabh...I find your blog very helpful and your experience valuable as I start my journey with SQL :)

    ReplyDelete
    Replies
    1. Welcome FESOLAI, I will try to make it more useful !!

      Delete
  6. Thanks a lot Saurabh for sharing such a great information

    ReplyDelete
  7. Saurabh, very helpful post,thanks

    ReplyDelete
  8. Nice Post Saurabh...Gives a Clear picture of Lazy writer and Checkpoint....

    ReplyDelete
  9. Nice Post.. Expecting more posts like this..

    ReplyDelete
  10. Could you explain what is meant by "cost factor of a page"?

    Thank you!

    ReplyDelete
  11. superb article, very informative cheers !!

    ReplyDelete
  12. It is really very useful analysis. Thanks for sharing such invaluable information. Very much appreciated.

    ReplyDelete