CheckPoint question

Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> > Is the quantity of data written to disk during a checkpoint, related to
> > the
> > size of the table? The checkpoint takes place when the log file is 70%
> > full.
> >
> > Compare these two identical files on separate servers except for rec amts.
> >
> > 10M rec file has checkpoint 47MG.
> > 100M rec file has checkpoint 81MG.
> >
> > Can I expect that as the tables get larger the checkpoint will become
> > larger?
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> > Is the quantity of data written to disk during a checkpoint, related to
>> > the
>> > size of the table? The checkpoint takes place when the log file is 70%
>> > full.
>> >
>> > Compare these two identical files on separate servers except for rec
>> > amts.
>> >
>> > 10M rec file has checkpoint 47MG.
>> > 100M rec file has checkpoint 81MG.
>> >
>> > Can I expect that as the tables get larger the checkpoint will become
>> > larger?
>> Checkpoint writes the dirty pages back to the database files, so the size
>> depends on the number of changes since the last checkpoint and how many
>> of
>> those pages have been flushed by the lazywriter thread. Also the
>> recovery
>> interval server parameter affects checkpoint size as well as the amount
>> of
>> memory on the server.
>> David
>>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> > I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> > no
> > change at all in the frequency of the flush or the amount of data flushed.
> > It always flushes when the log file is 70% full.
> >
> > I'd be really interested in manipulating the amount of data stored in
> > memory
> > and/or the frequency of the flush...
> >
> > Any advice much appreicated.
> >
> > Don
> >
> >
> > "David Browne" wrote:
> >
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> >> > Is the quantity of data written to disk during a checkpoint, related to
> >> > the
> >> > size of the table? The checkpoint takes place when the log file is 70%
> >> > full.
> >> >
> >> > Compare these two identical files on separate servers except for rec
> >> > amts.
> >> >
> >> > 10M rec file has checkpoint 47MG.
> >> > 100M rec file has checkpoint 81MG.
> >> >
> >> > Can I expect that as the tables get larger the checkpoint will become
> >> > larger?
> >>
> >> Checkpoint writes the dirty pages back to the database files, so the size
> >> depends on the number of changes since the last checkpoint and how many
> >> of
> >> those pages have been flushed by the lazywriter thread. Also the
> >> recovery
> >> interval server parameter affects checkpoint size as well as the amount
> >> of
> >> memory on the server.
> >>
> >> David
> >>
> >>
> >>
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>> The 70% deal is because you have the recovery mode set to SIMPLE or you
>> have
>> never done a proper FULL backup. The tran log will be truncated at 70%
>> full
>> in Simple mode. This in turn forces a checkpoint to occur. But that just
>> means that the amount of data in the tran log is still less than SQL
>> Server
>> thinks it will take to recover in 1 minute. If the log file was larger
>> you
>> would probably see checkpoints before the 70% full mark. What is the
>> reason
>> for wanting to change this? If checkpoints are causing issues with
>> performance you really need to address the source of the trouble and not
>> try
>> to tweak around it. That means placing the log file on a Raid 1 or raid
>> 10
>> by itself and a good amount of write back cache will help as well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>> > I've changed the recovery interval to 1, 100, 1000 respectively, and
>> > saw
>> > no
>> > change at all in the frequency of the flush or the amount of data
>> > flushed.
>> > It always flushes when the log file is 70% full.
>> >
>> > I'd be really interested in manipulating the amount of data stored in
>> > memory
>> > and/or the frequency of the flush...
>> >
>> > Any advice much appreicated.
>> >
>> > Don
>> >
>> >
>> > "David Browne" wrote:
>> >
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> >> > Is the quantity of data written to disk during a checkpoint, related
>> >> > to
>> >> > the
>> >> > size of the table? The checkpoint takes place when the log file is
>> >> > 70%
>> >> > full.
>> >> >
>> >> > Compare these two identical files on separate servers except for rec
>> >> > amts.
>> >> >
>> >> > 10M rec file has checkpoint 47MG.
>> >> > 100M rec file has checkpoint 81MG.
>> >> >
>> >> > Can I expect that as the tables get larger the checkpoint will
>> >> > become
>> >> > larger?
>> >>
>> >> Checkpoint writes the dirty pages back to the database files, so the
>> >> size
>> >> depends on the number of changes since the last checkpoint and how
>> >> many
>> >> of
>> >> those pages have been flushed by the lazywriter thread. Also the
>> >> recovery
>> >> interval server parameter affects checkpoint size as well as the
>> >> amount
>> >> of
>> >> memory on the server.
>> >>
>> >> David
>> >>
>> >>
>> >>
>>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David