SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Lazywriter and checkpoint


Difference between Lazywriter and checkpoint

Author
Message
Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
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
Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
No replies...Crying

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88569 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88569 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88569 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sachin Nandanwar
Sachin Nandanwar
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 2633
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
archana.dhagay
archana.dhagay
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 1
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
archana.dhagay
archana.dhagay
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 1
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":
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search