Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difference between Lazywriter and checkpoint Expand / Collapse
Author
Message
Posted Wednesday, January 19, 2011 1:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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?
Post #1049835
Posted Wednesday, January 19, 2011 5:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
No replies...
Post #1049954
Posted Wednesday, January 19, 2011 5:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1049959
Posted Wednesday, January 19, 2011 5:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Ok I will do that and wait. Hopefully someone comes up with an answer.
Post #1049980
Posted Wednesday, January 19, 2011 11:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1050273
Posted Thursday, January 20, 2011 5:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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 ?
Post #1050665
Posted Thursday, January 20, 2011 5:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1050673
Posted Thursday, January 20, 2011 6:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Just now I found this on MSDN

The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.


Post #1050704
Posted Saturday, May 11, 2013 12:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:28 PM
Points: 2, 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
Post #1451810
Posted Saturday, May 11, 2013 12:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:28 PM
Points: 2, 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":
Post #1451811
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse