Sequential Writes (t-logs) on a Separate Volume ?

I made a choice recently and that choice cost me more than I wanted it
to. Now I need to understand WHY I made the choice.

I configured my SQL Server database in such a way that the transaction
logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgroup
and I think I understand the rationale behind this: Transaction logs are
written are sequentially. Placing the transaction log on the same volume as
the data would cause the sequential writes of the transaction log to be
interrupted by the random read/writes of the data file. This would be
inefficient.

Here's my follow-up question: Let's say I have a single RAID 0+1 volume
that allows for 500 IOs/second. Let's say that my database uses 300
IOs/second including both read/writes of data and writes to the transaction
log. In this case, even if I place both the data file and the transaction
file on the same volume, I'm still not using all of the IOs/second that are
available to me. Why then would it make sense to place my transaction log on
a separate RAID 1 volume given that I'm not even making full use of my MAIN
volume?

Please show me the path.

Thank you.

The Oracle  wrote:
>     I made a choice recently and that choice cost me more than I wanted it
> to. Now I need to understand WHY I made the choice.
>
>     I configured my SQL Server database in such a way that the transaction
> logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgrou
p
> and I think I understand the rationale behind this: Transaction logs are
> written are sequentially. Placing the transaction log on the same volume a
s
> the data would cause the sequential writes of the transaction log to be
> interrupted by the random read/writes of the data file. This would be
> inefficient.
>
>     Here's my follow-up question: Let's say I have a single RAID 0+1 volum
e
> that allows for 500 IOs/second. Let's say that my database uses 300
> IOs/second including both read/writes of data and writes to the transactio
n
> log. In this case, even if I place both the data file and the transaction
> file on the same volume, I'm still not using all of the IOs/second that ar
e
> available to me. Why then would it make sense to place my transaction log 
on
> a separate RAID 1 volume given that I'm not even making full use of my MAI
N
> volume?

If you're using a hardware controller with its own stable write-back
cache (though it really should be a mirrored cache if you want the same
reliability guarantees you get with the mirrored disks), it might not
make any sense:  the cache firmware should be smart enough to gather up
the sequential log writes and batch them out lazily to the platters.

Otherwise, the advantage of isolating the transaction logs is that
you'll avoid any seek overhead when writing to them:  the head will
already be on-cylinder, and it'll just have to wait for the target disk
location to arrive under it.  This will cut your log-write latency by as
much as 2/3 (actually, it could be more if the database doesn't
prioritize its log writes over other writes that the log write might
then queue behind), and since a competent database will perform almost
all its other writes lazily you could conceivably see as much as a 3x
performance improvement (though in practical terms I'd guess you'd never
see more than a doubling in performance - e.g., because if your activity
is that hot you'll be seeing a complete disk revolution's worth of
log-write latency rather than the half-rev which is nominally 'average';
; even that level of improvement might be rare, given the way a good
database 'batches' up new log writes while waiting for the previous
write to complete, since long disk latencies then just allow more log
records to be included in the next log write and transfer overheads
start to make seek latency less important).

- bill

Bill Todd wrote:

[something twice]

Apologies:  Firefox has an annoying habit of bringing up a second copy
of itself when one double-clicks a folder (and my mouse is getting old
and bouncy).  If one does not notice this apparently both copies submit
the post...

- bill

On Wed, 30 Mar 2005 09:17:51 -0800, "The Oracle"
< 9thFloor@Building502
.TheMatrix.com> wrote:

>    I made a choice recently and that choice cost me more than I wanted it
>to. Now I need to understand WHY I made the choice.
>
>    I configured my SQL Server database in such a way that the transaction
>logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgroup
>and I think I understand the rationale behind this: Transaction logs are
>written are sequentially. Placing the transaction log on the same volume as
>the data would cause the sequential writes of the transaction log to be
>interrupted by the random read/writes of the data file. This would be
>inefficient.
>
>    Here's my follow-up question: Let's say I have a single RAID 0+1 volume
>that allows for 500 IOs/second. Let's say that my database uses 300
>IOs/second including both read/writes of data and writes to the transaction
>log. In this case, even if I place both the data file and the transaction
>file on the same volume, I'm still not using all of the IOs/second that are
>available to me. Why then would it make sense to place my transaction log o
n
>a separate RAID 1 volume given that I'm not even making full use of my MAIN
>volume?
>
>    Please show me the path.
>
>Thank you.
>

It is not a great idea to have the logs on the same LUN as the data
for the basic reason that with a LUN - even with RAID 1+0 (hopefully
you're striping your mirrored sets) - there are failure scenarios that
can take the LUN out of service.  There are OS failures, software
failures, etc. that may affect a LUN, but are less likely to affect
multiple LUNs.

(And you should have your backups to take care of multiple LUN
failures -  and of course, these should save the logs with the data in
an appropriate manner as well, just in case.)

There may be some folks who think that striping mirrored sets will
provide ultimate availability enough to keep them both on the same
LUN.  However, for folks who make a living in the High
Availability/Disaster Recovery/ Business Continuity biz, this is not
recommended.  Experience is often the best teacher in this case.

--- jls
The preceding message was personal opinion only.
I do not speak in any authorized capacity for anyone,
and certainly not my employer.