BULK INSERT: high disk queues (SQL Server 2000 SP4)

Hi,
I am using BUK INSERT to fill a table that usually contains about 100
mio. rows with about 10000 new rows per batch. This usually works fine
and takes about 1-2 seconds. Such bulk insert operation is executed
about every five seconds.
The problem is, that after about every 2 minutes one bulk exec will
take about 75 seconds, causing enourmous disk queues. After that
everything is fine again - for about two minutes.
There are some other strange facts:
1) this behaviour did only show up after the installation of service
pack 4;
2) this behaviour usually vanished after a couple of days with
continuous bulk inserts and would then only show up again after a sql
server server restart (but currently I have one server that shows the
problem since three weeks already);
3) I have this behaviour on both of our production databases but on one
server it simply disappeared (but will probably be there again after a
server restart) and on the other server it won't go away now.
I am quiet desperate with this situation and would be most thankful for
any hint.
Regards
DCIs this slowdown and high disk queues associated with a checkpoint?
Checkthe Sql perfmon counter for Checkpoint Pages Per second.
Andrew J. Kelly SQL MVP
<dc@.upsize.de> wrote in message
news:1147686058.831935.28980@.v46g2000cwv.googlegroups.com...
> Hi,
> I am using BUK INSERT to fill a table that usually contains about 100
> mio. rows with about 10000 new rows per batch. This usually works fine
> and takes about 1-2 seconds. Such bulk insert operation is executed
> about every five seconds.
> The problem is, that after about every 2 minutes one bulk exec will
> take about 75 seconds, causing enourmous disk queues. After that
> everything is fine again - for about two minutes.
> There are some other strange facts:
> 1) this behaviour did only show up after the installation of service
> pack 4;
> 2) this behaviour usually vanished after a couple of days with
> continuous bulk inserts and would then only show up again after a sql
> server server restart (but currently I have one server that shows the
> problem since three weeks already);
> 3) I have this behaviour on both of our production databases but on one
> server it simply disappeared (but will probably be there again after a
> server restart) and on the other server it won't go away now.
> I am quiet desperate with this situation and would be most thankful for
> any hint.
> Regards
> DC
>|||Dear Andrew,
I added the counter and yes: the very moment when the disk queues
start, the number of checkpoints per seconds jumps right up from about
0-2 per second to a number of 500-1000 checkpoints per second. I don't
know what that means - but sure hope you have another clue for me.
Regards
DC|||May I add to this: the secondary server, which is identical (physical
and configuration-wise) to the queuing server and also receives the
exact same bulk inserts, does only show a few checkpoint pages per
second and only every few minutes for only two or three seconds.|||Are both databases using the same recovery model ..?
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Also, is the recovery interval the same for both databases, sp_configure
HTH. Ryan
<dc@.upsize.de> wrote in message
news:1147700793.823997.289510@.g10g2000cwb.googlegroups.com...
> May I add to this: the secondary server, which is identical (physical
> and configuration-wise) to the queuing server and also receives the
> exact same bulk inserts, does only show a few checkpoint pages per
> second and only every few minutes for only two or three seconds.
>|||Both servers use the simple recovery model.
sp_configure output is also identical on both server, recovery interval
(min) is set to the default 0.|||In simple recovery mode CHECKPOINT happens automatically when the log
reaches 70% full. Are the log files the same size ..?
dbcc sqlperf(logspace)
HTH. Ryan
<dc@.upsize.de> wrote in message
news:1147703050.779437.211430@.u72g2000cwu.googlegroups.com...
> Both servers use the simple recovery model.
> sp_configure output is also identical on both server, recovery interval
> (min) is set to the default 0.
>|||Both machines are configured to use up to 50 GB of logspace (on three
different harddrives; harddrive size and raid type is the same on both
boxes).
The server with the massive queuing currently utilizes only 500 MB of
transaction log size (and displays that 20% of that is being used),
while the server which is working fine utilizes 37 GB of log files (and
reports that 97% of that is being used).|||> The server with the massive queuing currently utilizes only 500 MB of
> transaction log size (and displays that 20% of that is being used),
> while the server which is working fine utilizes 37 GB of log files (and
> reports that 97% of that is being used).
Seems that the physical file size for the log files are not the same, and yo
u are seeing a side
effect of having a "too small" log file. SQL Server does a checkpoint when t
he log file is 70% full,
which turn out to be very frequently on the system with the smaller file. Gr
ow the file and you will
probably see a difference.
Good catch by Ryan, btw...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<dc@.upsize.de> wrote in message news:1147706961.661923.196630@.u72g2000cwu.googlegroups.com..
.
> Both machines are configured to use up to 50 GB of logspace (on three
> different harddrives; harddrive size and raid type is the same on both
> boxes).
> The server with the massive queuing currently utilizes only 500 MB of
> transaction log size (and displays that 20% of that is being used),
> while the server which is working fine utilizes 37 GB of log files (and
> reports that 97% of that is being used).
>|||How can I force the log file to grow? I thought it would grow
automatically on demand. Can I for example set the log file to a fixed
size?
BTW: I have been watching dbcc sqlperf(logspace) on the server with the
massive checkpoints and queueing. The log file utilization was growing
from around 10 to 20 percent when the queueing started - it definitely
did not hit 70%.