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 you are seeing a side
effect of having a "too small" log file. SQL Server does a checkpoint when the log file is 70% full,
which turn out to be very frequently on the system with the smaller file. Grow 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%.|||If you do enough changes the recovery interval can force a checkpoint before
the 70% fullness is reached. You can change the size of the log file with
alter database command or thru Enterprise Manager. I would make both of them
the same size so you can rule that part out. Just because the machines seem
identical they may not be. One thing that affects how long a checkpoint
takes is the size and configuration of the cache on the disk controllers or
the SAN. IS the cache the same on both servers? Do you have write back
cache enabled and if so what is the read to write ratio?
--
Andrew J. Kelly SQL MVP
<dc@.upsize.de> wrote in message
news:1147726750.397611.19450@.i39g2000cwa.googlegroups.com...
> 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%.
>|||A slightly different approach would be to take control of the CHECKPOINT
yourself.
Try a different batch size (say 1000) and issue a CHECKPOINT after each
batch, that way the disk controller should receive a steady trickle of data
rather than being swamped
--
HTH. Ryan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ePFexUGeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> If you do enough changes the recovery interval can force a checkpoint
> before the 70% fullness is reached. You can change the size of the log
> file with alter database command or thru Enterprise Manager. I would make
> both of them the same size so you can rule that part out. Just because the
> machines seem identical they may not be. One thing that affects how long a
> checkpoint takes is the size and configuration of the cache on the disk
> controllers or the SAN. IS the cache the same on both servers? Do you
> have write back cache enabled and if so what is the read to write ratio?
> --
> Andrew J. Kelly SQL MVP
>
> <dc@.upsize.de> wrote in message
> news:1147726750.397611.19450@.i39g2000cwa.googlegroups.com...
>> 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%.
>|||Coincidentally this morning the queueing on the problematic server has
disappeared. Without obvious reason, after three weeks of problems.
However, I am sure that the problems will reappear once I restart the
server, since that has happened in the past.
When I manually launch a CHECKPOINT command on the server, it will
queue for about five minutes. And when I launch another checkpoint just
a few seconds later, it will again cause the disks to spin for a couple
of minutes.
dbcc sqlperf(logspace) now shows, that the database on the problematic
server is using 20 GB of log space. Maybe
ALTER DATABASE MyDB
MODIFY FILE
(NAME = MyLogfile,
SIZE = 40GB)
will help to avoid the automatic checkpoints once I have to restart one
of the servers.
I will also try to install the post SP4 hotfixes next time. I think
that these bulk insert related queues / checkpoints are a buggy sql
server behaviour since it did not appear before SP4.|||Today the problematic server started unwanted checkpoints and queueing
again. Which is strange, since in the past the servers were fine once
the queueing ceased - until the server had to be restarted.
I increased the logfile size with ALTER DATABASE - no change. I
installed the post-SP4 hotfixed - still no success.
The only countermeasure that had an effect is
sp_configure "recovery_interval", 60
RECONFIGURE WITH OVERRIDE
but this will still result in longer (about 3 minutes) queueing in
bigger intervals (~20 minutes).
Can I somehow find out what exactly is causing the checkpoint? Can I
see the checkpoint time and reason somewhere in the system tables? The
only difference between the two servers that I can see now, is that the
problematic server uses only a few percent of logspace (never hitting
70%), while the OK server always uses > 95% of the log space.
It is too bad that I cannot use non-logged or minimally-logged BULK
INSERTS (I cannot use TABLOCK because the table is also permanently
being queryied) since I believe that would make a difference in my
scenario.
I am wondering if SQL Server 2005 will perform better in my scenario
with permanent bulk inserts and also massive updating and simultaneous
querying. Data integrity is really not that important for my
application, but speed is. I wish I could deactivate all logging.|||Are you sure they are all in the same recovery model? In any case I really
think you are going down the wrong road. The bottom line is that if
checkpoints cause too much pain and last too long you do not have a proper
disk subsystem or configuration to handle the load.IS the log file on a RAID
1 or RAID 10 with no other types of files? What is the array configuration
for the data? How much and what is the configuration for the cache on the
controller or SAN processor?
--
Andrew J. Kelly SQL MVP
<dc@.upsize.de> wrote in message
news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> Today the problematic server started unwanted checkpoints and queueing
> again. Which is strange, since in the past the servers were fine once
> the queueing ceased - until the server had to be restarted.
> I increased the logfile size with ALTER DATABASE - no change. I
> installed the post-SP4 hotfixed - still no success.
> The only countermeasure that had an effect is
> sp_configure "recovery_interval", 60
> RECONFIGURE WITH OVERRIDE
> but this will still result in longer (about 3 minutes) queueing in
> bigger intervals (~20 minutes).
> Can I somehow find out what exactly is causing the checkpoint? Can I
> see the checkpoint time and reason somewhere in the system tables? The
> only difference between the two servers that I can see now, is that the
> problematic server uses only a few percent of logspace (never hitting
> 70%), while the OK server always uses > 95% of the log space.
> It is too bad that I cannot use non-logged or minimally-logged BULK
> INSERTS (I cannot use TABLOCK because the table is also permanently
> being queryied) since I believe that would make a difference in my
> scenario.
> I am wondering if SQL Server 2005 will perform better in my scenario
> with permanent bulk inserts and also massive updating and simultaneous
> querying. Data integrity is really not that important for my
> application, but speed is. I wish I could deactivate all logging.
>|||====================================="Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#jJQM#teGHA.4828@.TK2MSFTNGP05.phx.gbl...
> Are you sure they are all in the same recovery model? In any case I
really
> think you are going down the wrong road. The bottom line is that if
> checkpoints cause too much pain and last too long you do not have a proper
> disk subsystem or configuration to handle the load.IS the log file on a
RAID
> 1 or RAID 10 with no other types of files? What is the array
configuration
> for the data? How much and what is the configuration for the cache on the
> controller or SAN processor?
> --
> Andrew J. Kelly SQL MVP
>
> <dc@.upsize.de> wrote in message
> news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> > Today the problematic server started unwanted checkpoints and queueing
> > again. Which is strange, since in the past the servers were fine once
> > the queueing ceased - until the server had to be restarted.
> >
> > I increased the logfile size with ALTER DATABASE - no change. I
> > installed the post-SP4 hotfixed - still no success.
> >
> > The only countermeasure that had an effect is
> >
> > sp_configure "recovery_interval", 60
> >
> > RECONFIGURE WITH OVERRIDE
> >
> > but this will still result in longer (about 3 minutes) queueing in
> > bigger intervals (~20 minutes).
> >
> > Can I somehow find out what exactly is causing the checkpoint? Can I
> > see the checkpoint time and reason somewhere in the system tables? The
> > only difference between the two servers that I can see now, is that the
> > problematic server uses only a few percent of logspace (never hitting
> > 70%), while the OK server always uses > 95% of the log space.
> >
> > It is too bad that I cannot use non-logged or minimally-logged BULK
> > INSERTS (I cannot use TABLOCK because the table is also permanently
> > being queryied) since I believe that would make a difference in my
> > scenario.
> >
> > I am wondering if SQL Server 2005 will perform better in my scenario
> > with permanent bulk inserts and also massive updating and simultaneous
> > querying. Data integrity is really not that important for my
> > application, but speed is. I wish I could deactivate all logging.
> >
>|||I am ruling out disk performance, since the problem occurs on two
servers with identical hardware and configuration (including recovery
model) and it only occurs after the servers have been restarted. And on
one of the servers the problem seems to have manifested since it is
still there even weeks after restarting.
I never had this "too many checkpoints" problem before SP4. I am
thinking about going back to SP3. However, if I could somehow diagnose
why the checkpoints are occuring that would help. As mentioned before,
the situation got better with changing the "recovery_interval" to 300.
However, if I change the interval to 3000 or 30000 that does not seem
to make much of a difference, still a checkpoint will occur in 20-30
minute intervals.|||I'm not sure if this posted since it's been over 10 minutes and I got a blank
page after clicking the post button, so please ignore if my response is
already there:
Just out of curiosity, is there any reason why you're using the Simple
Recovery Model on a Production server that also receives a majority of their
inserts through Bulk-Insert? Why aren't you using the Bulk-Logged Recovery
Model which is designed for this particular situation? Each time you do a
Bulk-Insert of any magnitude, it is being logged, which will cause your
transaction logs to grow because each row that's being inserted is being
recorded, whereas using the Bulk-Logged Recovery will only record that you're
doing a Bulk-Insert, which will be less writes to your transaction log, less
auto-growth, and possibly less automatic checkpoints. I would follow the
suggestion of doing a manual checkpoint before each Bulk-Insert for piece of
mind though. Hopefully this helps.
Laurence
"Andrew J. Kelly" wrote:
> Are you sure they are all in the same recovery model? In any case I really
> think you are going down the wrong road. The bottom line is that if
> checkpoints cause too much pain and last too long you do not have a proper
> disk subsystem or configuration to handle the load.IS the log file on a RAID
> 1 or RAID 10 with no other types of files? What is the array configuration
> for the data? How much and what is the configuration for the cache on the
> controller or SAN processor?
> --
> Andrew J. Kelly SQL MVP
>
> <dc@.upsize.de> wrote in message
> news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> > Today the problematic server started unwanted checkpoints and queueing
> > again. Which is strange, since in the past the servers were fine once
> > the queueing ceased - until the server had to be restarted.
> >
> > I increased the logfile size with ALTER DATABASE - no change. I
> > installed the post-SP4 hotfixed - still no success.
> >
> > The only countermeasure that had an effect is
> >
> > sp_configure "recovery_interval", 60
> >
> > RECONFIGURE WITH OVERRIDE
> >
> > but this will still result in longer (about 3 minutes) queueing in
> > bigger intervals (~20 minutes).
> >
> > Can I somehow find out what exactly is causing the checkpoint? Can I
> > see the checkpoint time and reason somewhere in the system tables? The
> > only difference between the two servers that I can see now, is that the
> > problematic server uses only a few percent of logspace (never hitting
> > 70%), while the OK server always uses > 95% of the log space.
> >
> > It is too bad that I cannot use non-logged or minimally-logged BULK
> > INSERTS (I cannot use TABLOCK because the table is also permanently
> > being queryied) since I believe that would make a difference in my
> > scenario.
> >
> > I am wondering if SQL Server 2005 will perform better in my scenario
> > with permanent bulk inserts and also massive updating and simultaneous
> > querying. Data integrity is really not that important for my
> > application, but speed is. I wish I could deactivate all logging.
> >
>
>|||It is always OK for us to go back to the last backup. I thought that in
that scenario "simple" is the least demanding recovery model in terms
of logging. My understanding is that "bulk logged" mimics "simple" for
the bulk operations, but "full" for other operations. Since one of our
tables also receives a massive amount of updates, "bulk logged" mode
should result in more log file action.
However, out of desperation I switched to "bulk logged" on the
problematic server. That did not fix the problem, there is still a
checkpoint about every two minutes. I am currently working around by
setting the recovery_interval to 32767, which will still issue a
checkpoint or two per day and then block the server for about five
minutes (I wonder why the checkpoint does not take 32767 minutes since
that is what I thought the recovery_interval option indicates). I am
quiet sure that it will not take my database 32767 minutes to recover
after restarting the server but I am not willing to try.
I think that Sql Server is not calculating the time for a checkpoint
correctly under some circumstances with SP4 (don't know what exactly
the circumstances are since the other server is still operating
perfectly). I believe that the rows inserted with a bulk insert should
not trigger a checkpoint as quickly as they currently do, in other
words (sorry for my naive assumption) Sql Server takes to bulk inserted
rows for a reason to make a checkpoint appear.
Laurence schrieb:
> I'm not sure if this posted since it's been over 10 minutes and I got a blank
> page after clicking the post button, so please ignore if my response is
> already there:
> Just out of curiosity, is there any reason why you're using the Simple
> Recovery Model on a Production server that also receives a majority of their
> inserts through Bulk-Insert? Why aren't you using the Bulk-Logged Recovery
> Model which is designed for this particular situation? Each time you do a
> Bulk-Insert of any magnitude, it is being logged, which will cause your
> transaction logs to grow because each row that's being inserted is being
> recorded, whereas using the Bulk-Logged Recovery will only record that you're
> doing a Bulk-Insert, which will be less writes to your transaction log, less
> auto-growth, and possibly less automatic checkpoints. I would follow the
> suggestion of doing a manual checkpoint before each Bulk-Insert for piece of
> mind though. Hopefully this helps.
> Laurence
> "Andrew J. Kelly" wrote:
> > Are you sure they are all in the same recovery model? In any case I really
> > think you are going down the wrong road. The bottom line is that if
> > checkpoints cause too much pain and last too long you do not have a proper
> > disk subsystem or configuration to handle the load.IS the log file on a RAID
> > 1 or RAID 10 with no other types of files? What is the array configuration
> > for the data? How much and what is the configuration for the cache on the
> > controller or SAN processor?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <dc@.upsize.de> wrote in message
> > news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> > > Today the problematic server started unwanted checkpoints and queueing
> > > again. Which is strange, since in the past the servers were fine once
> > > the queueing ceased - until the server had to be restarted.
> > >
> > > I increased the logfile size with ALTER DATABASE - no change. I
> > > installed the post-SP4 hotfixed - still no success.
> > >
> > > The only countermeasure that had an effect is
> > >
> > > sp_configure "recovery_interval", 60
> > >
> > > RECONFIGURE WITH OVERRIDE
> > >
> > > but this will still result in longer (about 3 minutes) queueing in
> > > bigger intervals (~20 minutes).
> > >
> > > Can I somehow find out what exactly is causing the checkpoint? Can I
> > > see the checkpoint time and reason somewhere in the system tables? The
> > > only difference between the two servers that I can see now, is that the
> > > problematic server uses only a few percent of logspace (never hitting
> > > 70%), while the OK server always uses > 95% of the log space.
> > >
> > > It is too bad that I cannot use non-logged or minimally-logged BULK
> > > INSERTS (I cannot use TABLOCK because the table is also permanently
> > > being queryied) since I believe that would make a difference in my
> > > scenario.
> > >
> > > I am wondering if SQL Server 2005 will perform better in my scenario
> > > with permanent bulk inserts and also massive updating and simultaneous
> > > querying. Data integrity is really not that important for my
> > > application, but speed is. I wish I could deactivate all logging.
> > >
> >
> >
> >|||Hi
Don't forget, a checkpoint is a flush of dirty buffers to disk. This flush
will take a lot less time than the recovery interval setting. The recovery
interval setting is based on a log replay, not a simple flush to disk.
The amount of available RAM also play a role in the checkpoint interval
(e.g. if your DB is 20GB, and you have 2GB buffer RAM, if every page was
modified, ideally at least 10 checkpoints have to occur). If you are in
simple recovery mode, 70% log full generates a checkpoint.
Making sure that your disk subsystem is optimal is the answer to checkpoint
performance problems. Andrew listed the points to look for.
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<dc@.upsize.de> wrote in message
news:1149591061.614095.295260@.f6g2000cwb.googlegroups.com...
> It is always OK for us to go back to the last backup. I thought that in
> that scenario "simple" is the least demanding recovery model in terms
> of logging. My understanding is that "bulk logged" mimics "simple" for
> the bulk operations, but "full" for other operations. Since one of our
> tables also receives a massive amount of updates, "bulk logged" mode
> should result in more log file action.
> However, out of desperation I switched to "bulk logged" on the
> problematic server. That did not fix the problem, there is still a
> checkpoint about every two minutes. I am currently working around by
> setting the recovery_interval to 32767, which will still issue a
> checkpoint or two per day and then block the server for about five
> minutes (I wonder why the checkpoint does not take 32767 minutes since
> that is what I thought the recovery_interval option indicates). I am
> quiet sure that it will not take my database 32767 minutes to recover
> after restarting the server but I am not willing to try.
> I think that Sql Server is not calculating the time for a checkpoint
> correctly under some circumstances with SP4 (don't know what exactly
> the circumstances are since the other server is still operating
> perfectly). I believe that the rows inserted with a bulk insert should
> not trigger a checkpoint as quickly as they currently do, in other
> words (sorry for my naive assumption) Sql Server takes to bulk inserted
> rows for a reason to make a checkpoint appear.
>
> Laurence schrieb:
>> I'm not sure if this posted since it's been over 10 minutes and I got a
>> blank
>> page after clicking the post button, so please ignore if my response is
>> already there:
>> Just out of curiosity, is there any reason why you're using the Simple
>> Recovery Model on a Production server that also receives a majority of
>> their
>> inserts through Bulk-Insert? Why aren't you using the Bulk-Logged
>> Recovery
>> Model which is designed for this particular situation? Each time you do
>> a
>> Bulk-Insert of any magnitude, it is being logged, which will cause your
>> transaction logs to grow because each row that's being inserted is being
>> recorded, whereas using the Bulk-Logged Recovery will only record that
>> you're
>> doing a Bulk-Insert, which will be less writes to your transaction log,
>> less
>> auto-growth, and possibly less automatic checkpoints. I would follow the
>> suggestion of doing a manual checkpoint before each Bulk-Insert for piece
>> of
>> mind though. Hopefully this helps.
>> Laurence
>> "Andrew J. Kelly" wrote:
>> > Are you sure they are all in the same recovery model? In any case I
>> > really
>> > think you are going down the wrong road. The bottom line is that if
>> > checkpoints cause too much pain and last too long you do not have a
>> > proper
>> > disk subsystem or configuration to handle the load.IS the log file on a
>> > RAID
>> > 1 or RAID 10 with no other types of files? What is the array
>> > configuration
>> > for the data? How much and what is the configuration for the cache on
>> > the
>> > controller or SAN processor?
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > <dc@.upsize.de> wrote in message
>> > news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
>> > > Today the problematic server started unwanted checkpoints and
>> > > queueing
>> > > again. Which is strange, since in the past the servers were fine once
>> > > the queueing ceased - until the server had to be restarted.
>> > >
>> > > I increased the logfile size with ALTER DATABASE - no change. I
>> > > installed the post-SP4 hotfixed - still no success.
>> > >
>> > > The only countermeasure that had an effect is
>> > >
>> > > sp_configure "recovery_interval", 60
>> > >
>> > > RECONFIGURE WITH OVERRIDE
>> > >
>> > > but this will still result in longer (about 3 minutes) queueing in
>> > > bigger intervals (~20 minutes).
>> > >
>> > > Can I somehow find out what exactly is causing the checkpoint? Can I
>> > > see the checkpoint time and reason somewhere in the system tables?
>> > > The
>> > > only difference between the two servers that I can see now, is that
>> > > the
>> > > problematic server uses only a few percent of logspace (never hitting
>> > > 70%), while the OK server always uses > 95% of the log space.
>> > >
>> > > It is too bad that I cannot use non-logged or minimally-logged BULK
>> > > INSERTS (I cannot use TABLOCK because the table is also permanently
>> > > being queryied) since I believe that would make a difference in my
>> > > scenario.
>> > >
>> > > I am wondering if SQL Server 2005 will perform better in my scenario
>> > > with permanent bulk inserts and also massive updating and
>> > > simultaneous
>> > > querying. Data integrity is really not that important for my
>> > > application, but speed is. I wish I could deactivate all logging.
>> > >
>> >
>> >
>> >
>|||rahul sharma
rahul.sharma822@.gmail.com
*** Sent via Developersdex http://www.developersdex.com ***