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.
Reference:
Frequently Used Queries In SQL Server
Checkpoint: http://msdn.microsoft.com/en-us/library/ms188748.aspx
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 |
Reference:
Frequently Used Queries In SQL Server
Checkpoint: http://msdn.microsoft.com/en-us/library/ms188748.aspx
Really awesome collectoin. thanks a lot for knowledge sharing. I really understood the exact difference with your post.
ReplyDeleteThanks Ravinder, I will try to come up with more
DeleteFrom long time I am looking such a great post. Now I am clear about checkpoint and lazy writer
ReplyDeletegood to hear....
DeleteThank u so very much
ReplyDeleteNow m clear wid d differenc
Thank u once again
Welcome !!
DeleteGood article!
ReplyDeleteMSDN 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.
Thanks Sarjen , Appericiate your hawk eye on my blog. looking for more
DeleteThank for sharing this Saurabh...I find your blog very helpful and your experience valuable as I start my journey with SQL :)
ReplyDeleteWelcome FESOLAI, I will try to make it more useful !!
DeleteThanks a lot Saurabh for sharing such a great information
ReplyDeleteWelcome Amit !!
DeleteSaurabh, very helpful post,thanks
ReplyDeleteWelcome !!
DeleteNice Post Saurabh...Gives a Clear picture of Lazy writer and Checkpoint....
ReplyDeleteThanks Bikram!!
DeleteNice Post.. Expecting more posts like this..
ReplyDeleteWill keep truing :-)
DeleteNice explanation
ReplyDeleteThanks Narayana !!
DeleteCould you explain what is meant by "cost factor of a page"?
ReplyDeleteThank you!
superb article, very informative cheers !!
ReplyDeleteHi Dear,
ReplyDeleteI like Your Blog Very Much..I see Daily Your Blog ,is A Very Useful For me.
안전놀이터 추천 사설토토 검증사이트, 메이저놀이터 토토검문소입니다. "검문소 " 메이저놀이터 카지노 바카라사이트이며, 오직 안전하고 검증된 사설 토토사이트만 모았습니다. 먹튀검증이 전혀없는 인증이 완료된 업체들이며 먹튀시"【토토검문소】안전놀이터 추천 | 사설토토 검증사이트, 메이저놀이터" 전액보상해드립니다. 검증된 놀이터추천에 대해서는 오직 저희 검문소만이 가능하며, 절대 믿으셔도 됩니다
Visit Here - 【토토검문소】안전놀이터 추천 | 사설토토 검증사이트, 메이저놀이터
It is really very useful analysis. Thanks for sharing such invaluable information. Very much appreciated.
ReplyDelete