Balancing IO. File Groups vs Raid

[Bernie]

I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?

Thanks,
-Bernie

[Greg D. Moore \(Strider\)]

"Bernie" <ve*****@ix.netcom.com> wrote in message
news:e7*************************@posting.google.co m...

I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?
Can't answer all of those (partly I'm not fully awake. :)

But you absolutely want your logs on RAID 1 or RAID 10, NOT RAID 0.
Remember, RAID 0 has no redundancy. If you lose your transaction log, you
lose data. Or more specifically, you lose your ability to recover up to the
most recent point in time.

Ideally, I'd probably dedicate some LUN to the Data (this lun can comprise
of a RAID 5, RAID 10, etc, depending on needs, disk space, etc. (I prefer
if I have the space to use RAID 10), have another for TEMPDB (though I've
always cheated on this and put it on my DATA disk, and then a separate RAID
10 for logs.

The best reason I can see for separate filegroups is to separate out indices
to their own drive (we do this) or to break out very large tables so that
you can use some more advanced backup schemes.

That's my 2 cents at 1:00 AM.


Thanks,
-Bernie

[Ellen K.]

The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.

My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

On 11 Dec 2003 10:18:37 -0800, ve*****@ix.netcom.com (Bernie) wrote:

I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?

Thanks,
-Bernie

[Greg D. Moore \(Strider\)]

"Ellen K." <72************************@compuserve.com> wrote in message
news:nq********************************@4ax.com...

The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.
Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.
My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

[Ellen K.]

If it happens and inconveniences the users, we're that much closer to
a SAN. :)

On Mon, 15 Dec 2003 03:57:12 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenms.com> wrote:

"Ellen K." <72************************@compuserve.com> wrote in message
news:nq********************************@4ax.com.. .

The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.

Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.

My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

[Greg D. Moore \(Strider\)]

"Ellen K." <72************************@compuserve.com> wrote in message
news:bc********************************@4ax.com...

If it happens and inconveniences the users, we're that much closer to
a SAN. :)

Now see, thre I like how you think. :-)

[Ellen K.]

:)

Actually your previous post made me realize that I should organize
something the tech guys can use to make the database quickly reusable
in the event the singleton fails and I'm not around. So thank you!
:)

I think maybe I will create an empty filegroup "EMERGENCY" on the RAID
5 array, and make a script to drop all the nonclustered indexes from
the filegroup that's on the singleton and then recreate them in
EMERGENCY. Whaddya think?

On Tue, 16 Dec 2003 15:01:43 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenms.com> wrote:

"Ellen K." <72************************@compuserve.com> wrote in message
news:bc********************************@4ax.com.. .

If it happens and inconveniences the users, we're that much closer to
a SAN. :)

Now see, thre I like how you think. :-)