January 19, 2011 at 1:18 am
I am trying to understand the difference between lazy writer and checkpoint.If Lazywriter is supposed to "age out" dirty pages from data cache to the disk then what does checkpoint do ?
As per my understanding It is when the Lazywriter decides to flush the dirty pages to the disk that checkpoint occurs or are they two totally different processes?
If yes then can anyone please explain the difference?
And if they are same then does lazywriter also has the additional responsibilty of writing the pages log modified to the Tlog file?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 19, 2011 at 5:25 am
No replies...:crying:
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 19, 2011 at 5:35 am
Be patient. We're all volunteers with our own jobs, our own problems and we're answering questions in our spare time. Bumping your post after just 4 hours (4 hours where most of the posters here are asleep) is totally unnecessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 5:55 am
Ok I will do that and wait.:-) Hopefully someone comes up with an answer.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 19, 2011 at 11:46 am
Right, now that I have time to write complex answers...
Sachin Nandanwar (1/19/2011)
If Lazywriter is supposed to "age out" dirty pages from data cache to the disk then what does checkpoint do ?
Write all dirty pages down to reduce the time required for database recovery
As per my understanding It is when the Lazywriter decides to flush the dirty pages to the disk that checkpoint occurs or are they two totally different processes?
Two different processes. They both write dirty pages to disk, but that's where the similarity ends. They are run for different reasons with different aims. Checkpoint tries to write as many pages as fast as possible. Lazy writer tries to write as few as necessary.
http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 5:38 am
Checkpoint tries to write as many pages as fast as possible. Lazy writer tries to write as few as necessary.
Hey Gail thanks for the reply.
Now it is getting a bit for confusing for me if my understanding is right.
If I decide to set a recovery interval which will in turn affect the point where the checkpoint occurs then will the Lazywriter flush the dirty pages to the disk even though it does NOT feel memory pressure or wait for the checkpoint to occur or will itself flush them if it feels memory pressure ?
Also in your blog you stated
It does this to keep a certain amount of free pages available within the buffer pool for data that may be requested by other queries.
Can you please let me know what are those free pages in the buffer pool ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 20, 2011 at 5:44 am
Sachin Nandanwar (1/20/2011)
If I decide to set a recovery interval which will in turn affect the point where the checkpoint occurs then will the Lazywriter flush the dirty pages to the disk even though it does NOT feel memory pressure or wait for the checkpoint to occur or will itself flush them if it feels memory pressure ?
The lazy writer runs when there is memory pressure. No memory pressure, no lazy writer
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 6:17 am
Just now I found this on MSDN
The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
May 11, 2013 at 12:44 am
Difference between Checkpoint and LazyWriter
C: Flush dirty pages to Disk
L: Flush dirty pages to disk.
C: Flush only Data pages to disk
L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
C: Default, Occurs approximately every 1 minute
L: Occurs depending upon memory pressure and resource availability
C: Can be managed with sp_confige -recovery interval option
L:It is lazy, Sql server manages by its own.
C: Does not check the memory pressure
L:Monitor the memory pressure and try maintain the available free memory.
C: crash recovery process will be fast to read log as data file is updated.
L: No role in recovery
C: Occurs for any DDL statement
L: Occurs per requirement
C: Occurs before Backup/Detach command
L: Occurs per requirement
C: Depends upon the configuration setting, we can control.
L: Works on Least recent used pages and removed unused plans first, no user control.
C: for simple recovery it flush the tlog file after 70% full.
L: No effect on recovery model.
C: can manually /Forcefully run command “Checkpoint”
L: No command for Lazy Writer
C: Very Less performance impact
L: No performance impact
Checkpoint can be run manullay using command "Checkpoint":
Hope this helps you
May 11, 2013 at 12:45 am
Difference between Checkpoint and LazyWriter
C: Flush dirty pages to Disk
L: Flush dirty pages to disk.
C: Flush only Data pages to disk
L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
C: Default, Occurs approximately every 1 minute
L: Occurs depending upon memory pressure and resource availability
C: Can be managed with sp_confige -recovery interval option
L:It is lazy, Sql server manages by its own.
C: Does not check the memory pressure
L:Monitor the memory pressure and try maintain the available free memory.
C: crash recovery process will be fast to read log as data file is updated.
L: No role in recovery
C: Occurs for any DDL statement
L: Occurs per requirement
C: Occurs before Backup/Detach command
L: Occurs per requirement
C: Depends upon the configuration setting, we can control.
L: Works on Least recent used pages and removed unused plans first, no user control.
C: for simple recovery it flush the tlog file after 70% full.
L: No effect on recovery model.
C: can manually /Forcefully run command “Checkpoint”
L: No command for Lazy Writer
C: Very Less performance impact
L: No performance impact
Checkpoint can be run manullay using command "Checkpoint":
December 1, 2014 at 2:12 pm
You can refer below as well:
What Is The Difference Between Checkpoint And Lazy Writer : Interview Question
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply