CheckPoint causing long SP durations

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
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
>
|||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
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2BD564B3-C3A7-46D5-8F39-F6F4935CA69D@.microsoft.com...[vbcol=seagreen]
>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:

Checking if anything changed

I have a series of records that I periodically sum up, and then place those
sums in a master record for easy reporting. Every time the sum changes, I
note down that a change was made by updating a datetime in the master.
The trick is knowing if anything changed or not. I do this...
DECLARE @.didChange AS boolean
SET @.didChange = FALSE
IF ((@.newFilled IS null AND @.oldFilled IS NOT null) OR (@.newFilled IS NOT
null AND @.oldFilled IS null) OR (ROUND(@.newFilled, 2) <> ROUND(@.oldFilled,
2))) @.didChange = TRUE
there's a couple more IFs following. BTW, there's a syntax error in there
somewhere, I'm still trying to find it.
My question is whether or not I can simplify the if down to something
smaller. As you can see, it tests three cases...
1) the value was changed TO null from anything
2) the value was changed FROM null to anything
3) the value simply changed
In the past I tried a greatly simplified solution...
IF ISNULL(@.newFilled, 0) <> ISNULL(@.oldFilled, 0) THEN @.didChange = TRUE
The problem with this approach is that it "misses" changes from null to zero
or back. It just didn't work right.
So can I do this...
IF ISNULL(@.newFilled, -1) <> ISNULL(@.oldFilled, -1) THEN...
The values are always positive, so they can never be -1. Do you think this
is a good approach, or is there some edge case I'm forgetting about?
MauryAhh, the syntax error...
obviously that should be a tinyint (or bit I guess) and there needs to be a
SET
those are fixed.