Slow writes to transaction log 
Author Message
 Slow writes to transaction log

I've run into quite a frustrating problem.  I've been working on some
optimizations on my development machine (dual xeons, 1 gig ram, 2 ide
disks).  On my dev machine I have the data file and tran log on the same
disk (not on the primary OS partition though).  These changes (thanks to the
folks in the .programming group) have given me significant improvements. I
thought it was time to move them into production and reprocess our data.

The production server also has dual xeons, 2 gigs of ram, 4 scsi disks (no
RAID), etc.  It has nothing else going on.  After moving things to the
production server, performance nose-dived.  I went frow processing 3500
rows/sec to a few hundred/sec.  Processing a row in the applications context
consists of doing a search and two updates if there's a "match" (all within
a stored proc.  the stored proc is called from a .NET service).  On the
development server, I was doing approximately 1400 updates/sec; I topped out
at around 168/sec on the production box.  If I remove the two updates from
the stored procedure but keep the select in, it whips through things at a
rate of around 4500/sec.  Uncommenting the updates brings things to a
near-screeching halt.

I've been investigating this last night and most of today and I've hit a
dead end, but I think I've learned some useful things.  The problem seems
related to a disk i/o bottleneck wherever the transaction log is being
written.  If I keep the data and log files on the same disk, the problem is
on that disk.  If I move the tran log to another disk, the problem is there.
On the dev server (where I have them both together), Disk Write % stays
around 14% while doing 1400 disk writes/sec.  On the production server, Disk
Write % is 80% or higher while only doing 166 Disk Writes/sec.

I've tried this on two different physical disks so it's not a problem with
one of the drives.

I've also used 2 tools from sysinternals.com. Diskmon was filled with
IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
(there's only 1 partition on that disk).  The Sector number was increasing
sequentially but was repeated twice (once for each partition).  The length
of the write came up as "1", but I don't know the unit of the display.  I
also used filemon to verify that the file being written to was in fact the
log files (it was).

One other oddity is that created/modified/accessed properties of the log
file were not changing.  Maybe this is normal behavior?

Any help would be immensely appreciated.  Please let me know if any
additional info is required.  I've provided what I think to be useful.

-joe



Sat, 29 May 2004 06:30:01 GMT
 Slow writes to transaction log

Joe,

You absolutely have to separate the log files from any other activity to get
peak write performance with SQL Server.  Ideally you want a RAID 1 or 10 for
the logs with NOTHING else on that array.  For the data the more heads the
better. You will never get peak performance with just a few drives in the
array.  And unless you go with a RAID 5 or 10 you won't have any redundancy
if one drive fails.  Disks are too cheap these days to waste time with
performance issues relating to I/O bottlenecks.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive


Quote:
> I've run into quite a frustrating problem.  I've been working on some
> optimizations on my development machine (dual xeons, 1 gig ram, 2 ide
> disks).  On my dev machine I have the data file and tran log on the same
> disk (not on the primary OS partition though).  These changes (thanks to
the
> folks in the .programming group) have given me significant improvements. I
> thought it was time to move them into production and reprocess our data.

> The production server also has dual xeons, 2 gigs of ram, 4 scsi disks (no
> RAID), etc.  It has nothing else going on.  After moving things to the
> production server, performance nose-dived.  I went frow processing 3500
> rows/sec to a few hundred/sec.  Processing a row in the applications
context
> consists of doing a search and two updates if there's a "match" (all
within
> a stored proc.  the stored proc is called from a .NET service).  On the
> development server, I was doing approximately 1400 updates/sec; I topped
out
> at around 168/sec on the production box.  If I remove the two updates from
> the stored procedure but keep the select in, it whips through things at a
> rate of around 4500/sec.  Uncommenting the updates brings things to a
> near-screeching halt.

> I've been investigating this last night and most of today and I've hit a
> dead end, but I think I've learned some useful things.  The problem seems
> related to a disk i/o bottleneck wherever the transaction log is being
> written.  If I keep the data and log files on the same disk, the problem
is
> on that disk.  If I move the tran log to another disk, the problem is
there.
> On the dev server (where I have them both together), Disk Write % stays
> around 14% while doing 1400 disk writes/sec.  On the production server,
Disk
> Write % is 80% or higher while only doing 166 Disk Writes/sec.

> I've tried this on two different physical disks so it's not a problem with
> one of the drives.

> I've also used 2 tools from sysinternals.com. Diskmon was filled with
> IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
> (there's only 1 partition on that disk).  The Sector number was increasing
> sequentially but was repeated twice (once for each partition).  The length
> of the write came up as "1", but I don't know the unit of the display.  I
> also used filemon to verify that the file being written to was in fact the
> log files (it was).

> One other oddity is that created/modified/accessed properties of the log
> file were not changing.  Maybe this is normal behavior?

> Any help would be immensely appreciated.  Please let me know if any
> additional info is required.  I've provided what I think to be useful.

> -joe



Sat, 29 May 2004 07:16:07 GMT
 Slow writes to transaction log
Thanks for the replay Andrew.  I understand that I would achieve optimal
performance with a RAID array, but I don't think that's the problem here.
Is it normal that I am able to perform 1400 disk writes/sec to an IDE drive,
but only 166/sec to a SCSI drive?  Whether I put the log file with the data
file (I know that is bad) or separate the log file onto its own disk, it
doesn't make a difference.  That seems to indicate to me that something else
is awry.  There is no other activity on the disk with the tran log.  It's
silent besides the writes.  And the updates (what's causing the tran log
writes) are very simple.

I guess I expected at least the same and probably better disk performance
when moving from a very busy IDE system to a SCSI system with disks that
have no other responsibility.

Thanks again for your help.

-joe



Quote:
> Joe,

> You absolutely have to separate the log files from any other activity to
get
> peak write performance with SQL Server.  Ideally you want a RAID 1 or 10
for
> the logs with NOTHING else on that array.  For the data the more heads the
> better. You will never get peak performance with just a few drives in the
> array.  And unless you go with a RAID 5 or 10 you won't have any
redundancy
> if one drive fails.  Disks are too cheap these days to waste time with
> performance issues relating to I/O bottlenecks.

> --
> Andrew J. Kelly,  SQL Server MVP
> TargitInteractive



> > I've run into quite a frustrating problem.  I've been working on some
> > optimizations on my development machine (dual xeons, 1 gig ram, 2 ide
> > disks).  On my dev machine I have the data file and tran log on the same
> > disk (not on the primary OS partition though).  These changes (thanks to
> the
> > folks in the .programming group) have given me significant improvements.
 I
> > thought it was time to move them into production and reprocess our data.

> > The production server also has dual xeons, 2 gigs of ram, 4 scsi disks
(no
> > RAID), etc.  It has nothing else going on.  After moving things to the
> > production server, performance nose-dived.  I went frow processing 3500
> > rows/sec to a few hundred/sec.  Processing a row in the applications
> context
> > consists of doing a search and two updates if there's a "match" (all
> within
> > a stored proc.  the stored proc is called from a .NET service).  On the
> > development server, I was doing approximately 1400 updates/sec; I topped
> out
> > at around 168/sec on the production box.  If I remove the two updates
from
> > the stored procedure but keep the select in, it whips through things at
a
> > rate of around 4500/sec.  Uncommenting the updates brings things to a
> > near-screeching halt.

> > I've been investigating this last night and most of today and I've hit a
> > dead end, but I think I've learned some useful things.  The problem
seems
> > related to a disk i/o bottleneck wherever the transaction log is being
> > written.  If I keep the data and log files on the same disk, the problem
> is
> > on that disk.  If I move the tran log to another disk, the problem is
> there.
> > On the dev server (where I have them both together), Disk Write % stays
> > around 14% while doing 1400 disk writes/sec.  On the production server,
> Disk
> > Write % is 80% or higher while only doing 166 Disk Writes/sec.

> > I've tried this on two different physical disks so it's not a problem
with
> > one of the drives.

> > I've also used 2 tools from sysinternals.com. Diskmon was filled with
> > IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
> > (there's only 1 partition on that disk).  The Sector number was
increasing
> > sequentially but was repeated twice (once for each partition).  The
length
> > of the write came up as "1", but I don't know the unit of the display.
I
> > also used filemon to verify that the file being written to was in fact
the
> > log files (it was).

> > One other oddity is that created/modified/accessed properties of the log
> > file were not changing.  Maybe this is normal behavior?

> > Any help would be immensely appreciated.  Please let me know if any
> > additional info is required.  I've provided what I think to be useful.

> > -joe



Sat, 29 May 2004 07:24:53 GMT
 Slow writes to transaction log
So your test environment is different than your production, and you want to
know why they are different?   Maybe there is a bad controller or controller
setting, maybe they are slower discs.  The acronym "SCSI" isn't really
"faster" than the acronym "IDE".  The bottom line is multiple RAID SCSI
arrays are the way to go for high performance DBMS I/O.


Quote:
> Thanks for the replay Andrew.  I understand that I would achieve optimal
> performance with a RAID array, but I don't think that's the problem here.
> Is it normal that I am able to perform 1400 disk writes/sec to an IDE
drive,
> but only 166/sec to a SCSI drive?  Whether I put the log file with the
data
> file (I know that is bad) or separate the log file onto its own disk, it
> doesn't make a difference.  That seems to indicate to me that something
else
> is awry.  There is no other activity on the disk with the tran log.  It's
> silent besides the writes.  And the updates (what's causing the tran log
> writes) are very simple.

> I guess I expected at least the same and probably better disk performance
> when moving from a very busy IDE system to a SCSI system with disks that
> have no other responsibility.

> Thanks again for your help.

> -joe



> > Joe,

> > You absolutely have to separate the log files from any other activity to
> get
> > peak write performance with SQL Server.  Ideally you want a RAID 1 or 10
> for
> > the logs with NOTHING else on that array.  For the data the more heads
the
> > better. You will never get peak performance with just a few drives in
the
> > array.  And unless you go with a RAID 5 or 10 you won't have any
> redundancy
> > if one drive fails.  Disks are too cheap these days to waste time with
> > performance issues relating to I/O bottlenecks.

> > --
> > Andrew J. Kelly,  SQL Server MVP
> > TargitInteractive



> > > I've run into quite a frustrating problem.  I've been working on some
> > > optimizations on my development machine (dual xeons, 1 gig ram, 2 ide
> > > disks).  On my dev machine I have the data file and tran log on the
same
> > > disk (not on the primary OS partition though).  These changes (thanks
to
> > the
> > > folks in the .programming group) have given me significant
improvements.
>  I
> > > thought it was time to move them into production and reprocess our
data.

> > > The production server also has dual xeons, 2 gigs of ram, 4 scsi disks
> (no
> > > RAID), etc.  It has nothing else going on.  After moving things to the
> > > production server, performance nose-dived.  I went frow processing
3500
> > > rows/sec to a few hundred/sec.  Processing a row in the applications
> > context
> > > consists of doing a search and two updates if there's a "match" (all
> > within
> > > a stored proc.  the stored proc is called from a .NET service).  On
the
> > > development server, I was doing approximately 1400 updates/sec; I
topped
> > out
> > > at around 168/sec on the production box.  If I remove the two updates
> from
> > > the stored procedure but keep the select in, it whips through things
at
> a
> > > rate of around 4500/sec.  Uncommenting the updates brings things to a
> > > near-screeching halt.

> > > I've been investigating this last night and most of today and I've hit
a
> > > dead end, but I think I've learned some useful things.  The problem
> seems
> > > related to a disk i/o bottleneck wherever the transaction log is being
> > > written.  If I keep the data and log files on the same disk, the
problem
> > is
> > > on that disk.  If I move the tran log to another disk, the problem is
> > there.
> > > On the dev server (where I have them both together), Disk Write %
stays
> > > around 14% while doing 1400 disk writes/sec.  On the production
server,
> > Disk
> > > Write % is 80% or higher while only doing 166 Disk Writes/sec.

> > > I've tried this on two different physical disks so it's not a problem
> with
> > > one of the drives.

> > > I've also used 2 tools from sysinternals.com. Diskmon was filled with
> > > IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
> > > (there's only 1 partition on that disk).  The Sector number was
> increasing
> > > sequentially but was repeated twice (once for each partition).  The
> length
> > > of the write came up as "1", but I don't know the unit of the display.
> I
> > > also used filemon to verify that the file being written to was in fact
> the
> > > log files (it was).

> > > One other oddity is that created/modified/accessed properties of the
log
> > > file were not changing.  Maybe this is normal behavior?

> > > Any help would be immensely appreciated.  Please let me know if any
> > > additional info is required.  I've provided what I think to be useful.

> > > -joe



Sat, 29 May 2004 09:34:01 GMT
 Slow writes to transaction log
Maybe this is the wrong forum for this.  I'm trying to determine why I'm
facing almost an order of magnitude performance degradation.  I would
consider throwing more hardware at the problem if I felt I was getting the
most out of my current configuration.  I thought I would get responses along
the lines of "check this perf counter or check that counter".  Or I thought
someone might say "yea, I've seen that before.  Your 10000rpm SCSI disks are
slow or that Dell controller is a piece of garbage, upgrade it"

Again, maybe a RAID solution is what I need but I can't justify the
expenditure without some concrete evidence.

Thank you for your suggestions and again, I apologize if this is the wrong
place for this discussion.

-joe


Quote:
> So your test environment is different than your production, and you want
to
> know why they are different?   Maybe there is a bad controller or
controller
> setting, maybe they are slower discs.  The acronym "SCSI" isn't really
> "faster" than the acronym "IDE".  The bottom line is multiple RAID SCSI
> arrays are the way to go for high performance DBMS I/O.



> > Thanks for the replay Andrew.  I understand that I would achieve optimal
> > performance with a RAID array, but I don't think that's the problem
here.
> > Is it normal that I am able to perform 1400 disk writes/sec to an IDE
> drive,
> > but only 166/sec to a SCSI drive?  Whether I put the log file with the
> data
> > file (I know that is bad) or separate the log file onto its own disk, it
> > doesn't make a difference.  That seems to indicate to me that something
> else
> > is awry.  There is no other activity on the disk with the tran log.
It's
> > silent besides the writes.  And the updates (what's causing the tran log
> > writes) are very simple.

> > I guess I expected at least the same and probably better disk
performance
> > when moving from a very busy IDE system to a SCSI system with disks that
> > have no other responsibility.

> > Thanks again for your help.

> > -joe



> > > Joe,

> > > You absolutely have to separate the log files from any other activity
to
> > get
> > > peak write performance with SQL Server.  Ideally you want a RAID 1 or
10
> > for
> > > the logs with NOTHING else on that array.  For the data the more heads
> the
> > > better. You will never get peak performance with just a few drives in
> the
> > > array.  And unless you go with a RAID 5 or 10 you won't have any
> > redundancy
> > > if one drive fails.  Disks are too cheap these days to waste time with
> > > performance issues relating to I/O bottlenecks.

> > > --
> > > Andrew J. Kelly,  SQL Server MVP
> > > TargitInteractive



> > > > I've run into quite a frustrating problem.  I've been working on
some
> > > > optimizations on my development machine (dual xeons, 1 gig ram, 2
ide
> > > > disks).  On my dev machine I have the data file and tran log on the
> same
> > > > disk (not on the primary OS partition though).  These changes
(thanks
> to
> > > the
> > > > folks in the .programming group) have given me significant
> improvements.
> >  I
> > > > thought it was time to move them into production and reprocess our
> data.

> > > > The production server also has dual xeons, 2 gigs of ram, 4 scsi
disks
> > (no
> > > > RAID), etc.  It has nothing else going on.  After moving things to
the
> > > > production server, performance nose-dived.  I went frow processing
> 3500
> > > > rows/sec to a few hundred/sec.  Processing a row in the applications
> > > context
> > > > consists of doing a search and two updates if there's a "match" (all
> > > within
> > > > a stored proc.  the stored proc is called from a .NET service).  On
> the
> > > > development server, I was doing approximately 1400 updates/sec; I
> topped
> > > out
> > > > at around 168/sec on the production box.  If I remove the two
updates
> > from
> > > > the stored procedure but keep the select in, it whips through things
> at
> > a
> > > > rate of around 4500/sec.  Uncommenting the updates brings things to
a
> > > > near-screeching halt.

> > > > I've been investigating this last night and most of today and I've
hit
> a
> > > > dead end, but I think I've learned some useful things.  The problem
> > seems
> > > > related to a disk i/o bottleneck wherever the transaction log is
being
> > > > written.  If I keep the data and log files on the same disk, the
> problem
> > > is
> > > > on that disk.  If I move the tran log to another disk, the problem
is
> > > there.
> > > > On the dev server (where I have them both together), Disk Write %
> stays
> > > > around 14% while doing 1400 disk writes/sec.  On the production
> server,
> > > Disk
> > > > Write % is 80% or higher while only doing 166 Disk Writes/sec.

> > > > I've tried this on two different physical disks so it's not a
problem
> > with
> > > > one of the drives.

> > > > I've also used 2 tools from sysinternals.com. Diskmon was filled
with
> > > > IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
> > > > (there's only 1 partition on that disk).  The Sector number was
> > increasing
> > > > sequentially but was repeated twice (once for each partition).  The
> > length
> > > > of the write came up as "1", but I don't know the unit of the
display.
> > I
> > > > also used filemon to verify that the file being written to was in
fact
> > the
> > > > log files (it was).

> > > > One other oddity is that created/modified/accessed properties of the
> log
> > > > file were not changing.  Maybe this is normal behavior?

> > > > Any help would be immensely appreciated.  Please let me know if any
> > > > additional info is required.  I've provided what I think to be
useful.

> > > > -joe



Sat, 29 May 2004 10:10:01 GMT
 Slow writes to transaction log


Quote:
> Maybe this is the wrong forum for this.  I'm trying to determine why I'm
> facing almost an order of magnitude performance degradation.  I would
> consider throwing more hardware at the problem if I felt I was getting the
> most out of my current configuration.  I thought I would get responses
along
> the lines of "check this perf counter or check that counter".  Or I
thought
> someone might say "yea, I've seen that before.  Your 10000rpm SCSI disks
are
> slow or that Dell controller is a piece of garbage, upgrade it"

Couple of comments...

IDE can often beat SCSI, especially in sequential writing.

Though to be honest I'd be surprised if that's the case here (the change
seems just too large.)

Quote:
> Again, maybe a RAID solution is what I need but I can't justify the
> expenditure without some concrete evidence.

I think you DO have some sort of concrete evidence here though.

I'd suggest you look at a couple of things if possible:  how much if any
cache does the IDE drive have vs. the SCSI?

Did the IDE have some sort of write-back caching?  (which can be a problem
if it doesn't have a battery backup.)

Are the SCSI drives all on the same controller?  Does it support SCSI
disconnect?  (it should, but might be disabled.)

Just some thoughts.

Quote:
> Thank you for your suggestions and again, I apologize if this is the wrong
> place for this discussion.

> -joe



> > So your test environment is different than your production, and you want
> to
> > know why they are different?   Maybe there is a bad controller or
> controller
> > setting, maybe they are slower discs.  The acronym "SCSI" isn't really
> > "faster" than the acronym "IDE".  The bottom line is multiple RAID SCSI
> > arrays are the way to go for high performance DBMS I/O.



> > > Thanks for the replay Andrew.  I understand that I would achieve
optimal
> > > performance with a RAID array, but I don't think that's the problem
> here.
> > > Is it normal that I am able to perform 1400 disk writes/sec to an IDE
> > drive,
> > > but only 166/sec to a SCSI drive?  Whether I put the log file with the
> > data
> > > file (I know that is bad) or separate the log file onto its own disk,
it
> > > doesn't make a difference.  That seems to indicate to me that
something
> > else
> > > is awry.  There is no other activity on the disk with the tran log.
> It's
> > > silent besides the writes.  And the updates (what's causing the tran
log
> > > writes) are very simple.

> > > I guess I expected at least the same and probably better disk
> performance
> > > when moving from a very busy IDE system to a SCSI system with disks
that
> > > have no other responsibility.

> > > Thanks again for your help.

> > > -joe



> > > > Joe,

> > > > You absolutely have to separate the log files from any other
activity
> to
> > > get
> > > > peak write performance with SQL Server.  Ideally you want a RAID 1
or
> 10
> > > for
> > > > the logs with NOTHING else on that array.  For the data the more
heads
> > the
> > > > better. You will never get peak performance with just a few drives
in
> > the
> > > > array.  And unless you go with a RAID 5 or 10 you won't have any
> > > redundancy
> > > > if one drive fails.  Disks are too cheap these days to waste time
with
> > > > performance issues relating to I/O bottlenecks.

> > > > --
> > > > Andrew J. Kelly,  SQL Server MVP
> > > > TargitInteractive



> > > > > I've run into quite a frustrating problem.  I've been working on
> some
> > > > > optimizations on my development machine (dual xeons, 1 gig ram, 2
> ide
> > > > > disks).  On my dev machine I have the data file and tran log on
the
> > same
> > > > > disk (not on the primary OS partition though).  These changes
> (thanks
> > to
> > > > the
> > > > > folks in the .programming group) have given me significant
> > improvements.
> > >  I
> > > > > thought it was time to move them into production and reprocess our
> > data.

> > > > > The production server also has dual xeons, 2 gigs of ram, 4 scsi
> disks
> > > (no
> > > > > RAID), etc.  It has nothing else going on.  After moving things to
> the
> > > > > production server, performance nose-dived.  I went frow processing
> > 3500
> > > > > rows/sec to a few hundred/sec.  Processing a row in the
applications
> > > > context
> > > > > consists of doing a search and two updates if there's a "match"
(all

> > > > within
> > > > > a stored proc.  the stored proc is called from a .NET service).
On
> > the
> > > > > development server, I was doing approximately 1400 updates/sec; I
> > topped
> > > > out
> > > > > at around 168/sec on the production box.  If I remove the two
> updates
> > > from
> > > > > the stored procedure but keep the select in, it whips through
things
> > at
> > > a
> > > > > rate of around 4500/sec.  Uncommenting the updates brings things
to
> a
> > > > > near-screeching halt.

> > > > > I've been investigating this last night and most of today and I've
> hit
> > a
> > > > > dead end, but I think I've learned some useful things.  The
problem
> > > seems
> > > > > related to a disk i/o bottleneck wherever the transaction log is
> being
> > > > > written.  If I keep the data and log files on the same disk, the
> > problem
> > > > is
> > > > > on that disk.  If I move the tran log to another disk, the problem
> is
> > > > there.
> > > > > On the dev server (where I have them both together), Disk Write %
> > stays
> > > > > around 14% while doing 1400 disk writes/sec.  On the production
> > server,
> > > > Disk
> > > > > Write % is 80% or higher while only doing 166 Disk Writes/sec.

> > > > > I've tried this on two different physical disks so it's not a
> problem
> > > with
> > > > > one of the drives.

> > > > > I've also used 2 tools from sysinternals.com. Diskmon was filled
> with
> > > > > IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was
on
> > > > > (there's only 1 partition on that disk).  The Sector number was
> > > increasing
> > > > > sequentially but was repeated twice (once for each partition).
The
> > > length
> > > > > of the write came up as "1", but I don't know the unit of the
> display.
> > > I
> > > > > also used filemon to verify that the file being written to was in
> fact
> > > the
> > > > > log files (it was).

> > > > > One other oddity is that created/modified/accessed properties of
the
> > log
> > > > > file were not changing.  Maybe this is normal behavior?

> > > > > Any help would be immensely appreciated.  Please let me know if
any
> > > > > additional info is required.  I've provided what I think to be
> useful.

> > > > > -joe



Sat, 29 May 2004 21:05:31 GMT
 Slow writes to transaction log
Joe,

I didn't read the entire message, but perhaps the log is on RAID5 on the production server?
Raid5 is significantly slower for writes than one disk is...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> I've run into quite a frustrating problem.  I've been working on some
> optimizations on my development machine (dual xeons, 1 gig ram, 2 ide
> disks).  On my dev machine I have the data file and tran log on the same
> disk (not on the primary OS partition though).  These changes (thanks to the
> folks in the .programming group) have given me significant improvements. I
> thought it was time to move them into production and reprocess our data.

> The production server also has dual xeons, 2 gigs of ram, 4 scsi disks (no
> RAID), etc.  It has nothing else going on.  After moving things to the
> production server, performance nose-dived.  I went frow processing 3500
> rows/sec to a few hundred/sec.  Processing a row in the applications context
> consists of doing a search and two updates if there's a "match" (all within
> a stored proc.  the stored proc is called from a .NET service).  On the
> development server, I was doing approximately 1400 updates/sec; I topped out
> at around 168/sec on the production box.  If I remove the two updates from
> the stored procedure but keep the select in, it whips through things at a
> rate of around 4500/sec.  Uncommenting the updates brings things to a
> near-screeching halt.

> I've been investigating this last night and most of today and I've hit a
> dead end, but I think I've learned some useful things.  The problem seems
> related to a disk i/o bottleneck wherever the transaction log is being
> written.  If I keep the data and log files on the same disk, the problem is
> on that disk.  If I move the tran log to another disk, the problem is there.
> On the dev server (where I have them both together), Disk Write % stays
> around 14% while doing 1400 disk writes/sec.  On the production server, Disk
> Write % is 80% or higher while only doing 166 Disk Writes/sec.

> I've tried this on two different physical disks so it's not a problem with
> one of the drives.

> I've also used 2 tools from sysinternals.com. Diskmon was filled with
> IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
> (there's only 1 partition on that disk).  The Sector number was increasing
> sequentially but was repeated twice (once for each partition).  The length
> of the write came up as "1", but I don't know the unit of the display.  I
> also used filemon to verify that the file being written to was in fact the
> log files (it was).

> One other oddity is that created/modified/accessed properties of the log
> file were not changing.  Maybe this is normal behavior?

> Any help would be immensely appreciated.  Please let me know if any
> additional info is required.  I've provided what I think to be useful.

> -joe



Sat, 29 May 2004 21:33:03 GMT
 Slow writes to transaction log
No.  It is a simple SCSI configuration.  There is no RAID involved.

Adaptec Ultra160/m controllers writing to Seagate Cheetah 10Krpm disks.

thanks
-joe



Quote:
> Joe,

> I didn't read the entire message, but perhaps the log is on RAID5 on the
production server?
> Raid5 is significantly slower for writes than one disk is...

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...
Quote:




Quote:
> > I've run into quite a frustrating problem.  I've been working on some
> > optimizations on my development machine (dual xeons, 1 gig ram, 2 ide
> > disks).  On my dev machine I have the data file and tran log on the same
> > disk (not on the primary OS partition though).  These changes (thanks to
the
> > folks in the .programming group) have given me significant improvements.
I
> > thought it was time to move them into production and reprocess our data.

> > The production server also has dual xeons, 2 gigs of ram, 4 scsi disks
(no
> > RAID), etc.  It has nothing else going on.  After moving things to the
> > production server, performance nose-dived.  I went frow processing 3500
> > rows/sec to a few hundred/sec.  Processing a row in the applications
context
> > consists of doing a search and two updates if there's a "match" (all
within
> > a stored proc.  the stored proc is called from a .NET service).  On the
> > development server, I was doing approximately 1400 updates/sec; I topped
out
> > at around 168/sec on the production box.  If I remove the two updates
from
> > the stored procedure but keep the select in, it whips through things at
a
> > rate of around 4500/sec.  Uncommenting the updates brings things to a
> > near-screeching halt.

> > I've been investigating this last night and most of today and I've hit a
> > dead end, but I think I've learned some useful things.  The problem
seems
> > related to a disk i/o bottleneck wherever the transaction log is being
> > written.  If I keep the data and log files on the same disk, the problem
is
> > on that disk.  If I move the tran log to another disk, the problem is
there.
> > On the dev server (where I have them both together), Disk Write % stays
> > around 14% while doing 1400 disk writes/sec.  On the production server,
Disk
> > Write % is 80% or higher while only doing 166 Disk Writes/sec.

> > I've tried this on two different physical disks so it's not a problem
with
> > one of the drives.

> > I've also used 2 tools from sysinternals.com. Diskmon was filled with
> > IRP_MJ_WRITE to Partition 0 & 1 to the disk that the tran log was on
> > (there's only 1 partition on that disk).  The Sector number was
increasing
> > sequentially but was repeated twice (once for each partition).  The
length
> > of the write came up as "1", but I don't know the unit of the display.
I
> > also used filemon to verify that the file being written to was in fact
the
> > log files (it was).

> > One other oddity is that created/modified/accessed properties of the log
> > file were not changing.  Maybe this is normal behavior?

> > Any help would be immensely appreciated.  Please let me know if any
> > additional info is required.  I've provided what I think to be useful.

> > -joe



Sun, 30 May 2004 01:02:08 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Stop Transaction Logs backups writing to NT Event Logs

2. Transaction Log Backups Are Slower When The First Portion Of The Log Sequence Number Increments

3. PL/SQL log sync waits, log file parallel write waits and redo writes

4. iimklog: Unable to continue writing transaction log.

5. How to Write/Read Transaction Log

6. Disable Transaction Log Writes?

7. What is written to the Transaction Log and When

8. Error -121 (cannot write log record) on update in Informix SE 5.01 in a Transaction

9. Avoid writing entry to transaction log??

10. Updating records with out writing in to the Transaction log

11. Writes to transaction logs, Permission Denied

12. Help!How to speed up query by not writing to Transaction log


 
Powered by phpBB® Forum Software