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\)]

Re: Balancing IO. File Groups vs Raid


"Bernie" <velivis@ix.net com.com> wrote in message
news:e707e6a.03 12111018.475c19 9a@posting.goog le.com...[color=blue]
> 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?[/color]

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.



[color=blue]
>
> Thanks,
> -Bernie[/color]

[Ellen K.]

Re: Balancing IO. File Groups vs Raid

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, velivis@ix.netc om.com (Bernie) wrote:
[color=blue]
>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[/color]

[Greg D. Moore \(Strider\)]

Re: Balancing IO. File Groups vs Raid


"Ellen K." <72322.enno.ess peeayem.1016@co mpuserve.com> wrote in message
news:nqnntvk42k 1b5fjq2ui7f8fa0 huttaps42@4ax.c om...[color=blue]
> 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.[/color]

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.[color=blue]
>
> 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.[/color]

[Ellen K.]

Re: Balancing IO. File Groups vs Raid

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\)"
<mooregr@greenm s.com> wrote:
[color=blue]
>
>"Ellen K." <72322.enno.ess peeayem.1016@co mpuserve.com> wrote in message
>news:nqnntvk42 k1b5fjq2ui7f8fa 0huttaps42@4ax. com...[color=green]
>> 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.[/color]
>
>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.[color=green]
>>
>> 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.[/color]
>[/color]

[Greg D. Moore \(Strider\)]

Re: Balancing IO. File Groups vs Raid


"Ellen K." <72322.enno.ess peeayem.1016@co mpuserve.com> wrote in message
news:bcgttvkt2u 15bnfmlr9m6d1a1 u6f2256k0@4ax.c om...[color=blue]
> If it happens and inconveniences the users, we're that much closer to
> a SAN. :)
>[/color]


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

[Ellen K.]

Re: Balancing IO. File Groups vs Raid

:)

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\)"
<mooregr@greenm s.com> wrote:
[color=blue]
>
>"Ellen K." <72322.enno.ess peeayem.1016@co mpuserve.com> wrote in message
>news:bcgttvkt2 u15bnfmlr9m6d1a 1u6f2256k0@4ax. com...[color=green]
>> If it happens and inconveniences the users, we're that much closer to
>> a SAN. :)
>>[/color]
>
>
>Now see, thre I like how you think. :-)
>
>[/color]