CheckPoint causing long SP durations

1 Mar 2006 9:30 PM

donsql22222

There are thousands of continuous SELECTS on the tables per min and durations
are good and low.
Periodically, there are massive INSERTS done in batch that number in the
many thousands.
While monitoring the Checkpoint Pages/Sec counter, whenever the checkpoint
is issued by SQL, the Duration times (which are monitored in Profiler) go up
dramatically causing client issues and then the duration times go back down
to low after the checkpoint has flushed the dirty pages to disk.

Any suggestions? IO to disk?

Don
SQL 2000 SP4

3 Mar 2006 10:31 PM

Linchi Shea

A checkpoint flushes all the dirty pages of the current database to disk.
When you have a lot of modified pages (as in your case when you do massive
inserts via a batch job), it can put considerable strain on the I/O subsystem.

If checkpoints are a serious problem for you, upgrading to a better your I/O
subsystem will always help :-) You can also consider pacing your INSERTs if
you can afford lengthening your batch processing time.

Note that there is a reason why people usually schedule their batch jobs
during a relatively less busy time period.

Linchi

Show quote

"donsql22222" wrote:

> There are thousands of continuous SELECTS on the tables per min and durations
> are good and low.
> Periodically, there are massive INSERTS done in batch that number in the
> many thousands.
> While monitoring the Checkpoint Pages/Sec counter, whenever the checkpoint
> is issued by SQL, the Duration times (which are monitored in Profiler) go up
> dramatically causing client issues and then the duration times go back down
> to low after the checkpoint has flushed the dirty pages to disk.
>
> Any suggestions? IO to disk?
>
> Don
> SQL 2000 SP4
>

3 Mar 2006 11:34 PM

Andrew J. Kelly

In addition to what Linchi says (which should be heeded by the way) make
sure your log files are not on the same physical drive array as the data or
tempdb.

--
Andrew J. Kelly SQL MVP

Show quote

"Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message
news:2BD564B3-C3A7-46D5-8F39-F6F4935CA69D@microsoft.com...
>A checkpoint flushes all the dirty pages of the current database to disk.
> When you have a lot of modified pages (as in your case when you do massive
> inserts via a batch job), it can put considerable strain on the I/O
> subsystem.
>
> If checkpoints are a serious problem for you, upgrading to a better your
> I/O
> subsystem will always help :-) You can also consider pacing your INSERTs
> if
> you can afford lengthening your batch processing time.
>
> Note that there is a reason why people usually schedule their batch jobs
> during a relatively less busy time period.
>
> Linchi
>
> "donsql22222" wrote:
>
>> There are thousands of continuous SELECTS on the tables per min and
>> durations
>> are good and low.
>> Periodically, there are massive INSERTS done in batch that number in the
>> many thousands.
>> While monitoring the Checkpoint Pages/Sec counter, whenever the
>> checkpoint
>> is issued by SQL, the Duration times (which are monitored in Profiler) go
>> up
>> dramatically causing client issues and then the duration times go back
>> down
>> to low after the checkpoint has flushed the dirty pages to disk.
>>
>> Any suggestions? IO to disk?
>>
>> Don
>> SQL 2000 SP4
>>