question about checkpoints and cache

I'm trying to understand sql server architecture and I had some basic
questions for you veterans (tough questions for me)
1) My understanding of how the architecture works: data is modified,
before and after
copies of the modified data are first written to the transaction log. After
the log is updated,
the data is then modified in the data cache. When a checkpoint occurs, the
contents of the data cache is flushed to disk and at this point the disk
files and the transaction log are now in sync.
Am I on target with my understanding?
2) I read that in Simple Recovery, an automatic checkpoint occurs once a
minute to flush dirty cached data to the database. What about in Full
Recovery? How often does sql server perform a checkpoint?
3) When I select/delete/insert/update a few rows from a table, are all the
data for the table put into the data cache or just the rows I processed? At
this point, that memory that held the data is freed up, correct? A good
brief overview on this would be great.
4) In the procedure cache, the lazy writer sweeps through and ages out
cached queries if they haven't been used in a while. When that happens,
that memory is freed up for use by new execution plans, correct?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:IJOdnfU-irDX3SLenZ2dnUVZ_tGdnZ2d@.comcast.com...
> I'm trying to understand sql server architecture and I had some basic
> questions for you veterans (tough questions for me)
> 1) My understanding of how the architecture works: data is modified,
> before and after
> copies of the modified data are first written to the transaction log.
> After
> the log is updated,
> the data is then modified in the data cache. When a checkpoint occurs,
> the
> contents of the data cache is flushed to disk and at this point the disk
> files and the transaction log are now in sync.
> Am I on target with my understanding?
Yes.

> 2) I read that in Simple Recovery, an automatic checkpoint occurs once a
> minute to flush dirty cached data to the database. What about in Full
> Recovery? How often does sql server perform a checkpoint?
SQL Server 2005 Books Online BOL (same for SQL 2000):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/98a80238-7409-4708-8a7d-5defd9957185.htm
The interval between automatic checkpoints also depends on the recovery
model:
If the database is using either the full or bulk-logged recovery model, an
automatic checkpoint is generated whenever the number of log records reaches
the number the Database Engine estimates it can process during the time
specified in the recovery interval option.
If the database is using the simple recovery model, an automatic checkpoint
is generated whenever the number of log records reaches the lesser of these
two values:
The log becomes 70 percent full.
The number of log records reaches the number the Database Engine estimates
it can process during the time specified in the recovery interval option.

> 3) When I select/delete/insert/update a few rows from a table, are all
> the
> data for the table put into the data cache or just the rows I processed?
> At
> this point, that memory that held the data is freed up, correct? A good
> brief overview on this would be great.
>
The thing to remember here is that data pages are _only_ read from cache.
If your query needs to read a page, the page must be put in the cache first.

> 4) In the procedure cache, the lazy writer sweeps through and ages out
> cached queries if they haven't been used in a while. When that happens,
> that memory is freed up for use by new execution plans, correct?
>
This has changed in SQL 2005, which has a unified cache architecture for all
caches other than the data cache. But basically rarely used plans are aged
out to make room for new plans, and the overall size of the cache is
adjusted in case of server memory pressure.
David
|||> 1) My understanding of how the architecture works: data is modified,
> before and after
> copies of the modified data are first written to the transaction log.
> After
> the log is updated,
> the data is then modified in the data cache. When a checkpoint occurs,
> the
> contents of the data cache is flushed to disk and at this point the disk
> files and the transaction log are now in sync.
> Am I on target with my understanding?
To expand on David's response, data pages are also written by worker threads
and the lazy writer. Data is always written to the transaction log first.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:IJOdnfU-irDX3SLenZ2dnUVZ_tGdnZ2d@.comcast.com...
> I'm trying to understand sql server architecture and I had some basic
> questions for you veterans (tough questions for me)
> 1) My understanding of how the architecture works: data is modified,
> before and after
> copies of the modified data are first written to the transaction log.
> After
> the log is updated,
> the data is then modified in the data cache. When a checkpoint occurs,
> the
> contents of the data cache is flushed to disk and at this point the disk
> files and the transaction log are now in sync.
> Am I on target with my understanding?
> 2) I read that in Simple Recovery, an automatic checkpoint occurs once a
> minute to flush dirty cached data to the database. What about in Full
> Recovery? How often does sql server perform a checkpoint?
> 3) When I select/delete/insert/update a few rows from a table, are all
> the
> data for the table put into the data cache or just the rows I processed?
> At
> this point, that memory that held the data is freed up, correct? A good
> brief overview on this would be great.
> 4) In the procedure cache, the lazy writer sweeps through and ages out
> cached queries if they haven't been used in a while. When that happens,
> that memory is freed up for use by new execution plans, correct?
>
|||Also note C/O Andrew J. Kelly SQL MVP
http://www.kbalertz.com/Feedback_909369.aspx
Paul
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e4MPVK0EGHA.1312@.TK2MSFTNGP09.phx.gbl...
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:IJOdnfU-irDX3SLenZ2dnUVZ_tGdnZ2d@.comcast.com...
> Yes.
>
> SQL Server 2005 Books Online BOL (same for SQL 2000):
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/98a80238-7409-4708-8a7d-5defd9957185.htm
>
> The interval between automatic checkpoints also depends on the recovery
> model:
> If the database is using either the full or bulk-logged recovery model, an
> automatic checkpoint is generated whenever the number of log records
> reaches the number the Database Engine estimates it can process during the
> time specified in the recovery interval option.
>
> If the database is using the simple recovery model, an automatic
> checkpoint is generated whenever the number of log records reaches the
> lesser of these two values:
>
> The log becomes 70 percent full.
>
> The number of log records reaches the number the Database Engine estimates
> it can process during the time specified in the recovery interval option.
>
>
> The thing to remember here is that data pages are _only_ read from cache.
> If your query needs to read a page, the page must be put in the cache
> first.
>
> This has changed in SQL 2005, which has a unified cache architecture for
> all caches other than the data cache. But basically rarely used plans are
> aged out to make room for new plans, and the overall size of the cache is
> adjusted in case of server memory pressure.
> David
>