Hi all
I would like to share my own experience with performance tests in raid 10
configurations. From what i read from web was that # of iops directly
proportional to number of spindles in raid 10 conf and 1/2 of write iops
capacity of all drives.
what i am getting is differnt. I am using iometer with 50/50 read writes,
sequent/ random access, 8k buffer size.
read write
single drive 72 71 = 144
mirrored pair 184 196 = 381
4 dr. array 210 213 = 424
10 dr. array 264 259 = 524
14 dr. array prjected iops = 550

so yes, you get an improvement, but the price of it is not proportional to
number of drives at all.
I have so many drives only for the purpose of better performance, 1 drive
can hold all db files easily.
Here is my dilemma: create 7 mirrored pairs - each will perform 50% slower
than raid of 14 drive, but i will have 7 of them and can split db's to reside
on each of it.
Or
to have all db's residing on 1 14 drives raid. And key here would be not
combined improvement - it's only 50% to compare with single pair, but timing.
The idea is that spikes of activity will get 50% performance to compare with
mirrored pair.

It's OLTP server and does not have prolonged acitvity periods, rather sharp
and frequent spikes.
Tempdb and logs will be on different disks in both situations, what would be
your choice?

Please tell us more about the hardware you were using for the test.

The type of RAID controller is important, including the interface
between the controller and the server. If the controller is
saturated, or the bus between the controller and the server, then
adding drives will be futile when trying for more performance. I
would really like to know more.

Thanks!

Roy Harvey
Beacon Falls, CT

On Wed, 10 Sep 2008 14:55:00 -0700, Gene.
<Gene@discussions.microsoft.com> wrote:

>Hi all
>I would like to share my own experience with performance tests in raid 10
>configurations. From what i read from web was that # of iops directly
>proportional to number of spindles in raid 10 conf and 1/2 of write iops
>capacity of all drives.
>what i am getting is differnt. I am using iometer with 50/50 read writes,
>sequent/ random access, 8k buffer size.
> read write
>single drive 72 71 = 144
>mirrored pair 184 196 = 381
>4 dr. array 210 213 = 424
>10 dr. array 264 259 = 524
>14 dr. array prjected iops = 550
>
>so yes, you get an improvement, but the price of it is not proportional to
>number of drives at all.
>I have so many drives only for the purpose of better performance, 1 drive
>can hold all db files easily.
>Here is my dilemma: create 7 mirrored pairs - each will perform 50% slower
>than raid of 14 drive, but i will have 7 of them and can split db's to reside
>on each of it.
>Or
>to have all db's residing on 1 14 drives raid. And key here would be not
>combined improvement - it's only 50% to compare with single pair, but timing.
>The idea is that spikes of activity will get 50% performance to compare with
>mirrored pair.
>
>It's OLTP server and does not have prolonged acitvity periods, rather sharp
>and frequent spikes.
>Tempdb and logs will be on different disks in both situations, what would be
>your choice?

Hi Roy

I use dell 2900 server with 2 controllers: perc 5/i and perc 6/e - bult-in
and external controllers. There is not much choice for dell hardware - only
these 2. 6/e has 256mb but will be replaced for 512. I still think it won't
make difference. Controllers are not saturated - it's brand new and nothing
is running on it. I just try to see what are best options. What i also
learned is that these controllers do not support raid 0+1 conf or if they
did, dell does not recommend to use it because of low fault tollerance. Now,
raid 10 of 10 drives consist of 5 'spans' - mirrored pairs joined to produce
1 raid. No wonder that overhead of writing to it consist of complicated
algoritm writing to each pair. Representative from hardware tech support was
not even sure if 0+1 will tolerate 1 drive faulure - of cause it will. Their
tech level is very low. They can't provide specific IOPS numbers for
different configurations and start talking like 'depending of application'
b..l. I am dissappointed with their lack of skills.
i am leaning to use mirrored pairs. If i see saturation, i could attach more
pairs and move active tables around. Original idea to have big raid which is
7 times better than mirrored pair does not live.

"Roy Harvey (SQL Server MVP)" wrote:

> Please tell us more about the hardware you were using for the test.
>
> The type of RAID controller is important, including the interface
> between the controller and the server. If the controller is
> saturated, or the bus between the controller and the server, then
> adding drives will be futile when trying for more performance. I
> would really like to know more.
>
> Thanks!
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 10 Sep 2008 14:55:00 -0700, Gene.
> <Gene@discussions.microsoft.com> wrote:
>
> >Hi all
> >I would like to share my own experience with performance tests in raid 10
> >configurations. From what i read from web was that # of iops directly
> >proportional to number of spindles in raid 10 conf and 1/2 of write iops
> >capacity of all drives.
> >what i am getting is differnt. I am using iometer with 50/50 read writes,
> >sequent/ random access, 8k buffer size.
> > read write
> >single drive 72 71 = 144
> >mirrored pair 184 196 = 381
> >4 dr. array 210 213 = 424
> >10 dr. array 264 259 = 524
> >14 dr. array prjected iops = 550
> >
> >so yes, you get an improvement, but the price of it is not proportional to
> >number of drives at all.
> >I have so many drives only for the purpose of better performance, 1 drive
> >can hold all db files easily.
> >Here is my dilemma: create 7 mirrored pairs - each will perform 50% slower
> >than raid of 14 drive, but i will have 7 of them and can split db's to reside
> >on each of it.
> >Or
> >to have all db's residing on 1 14 drives raid. And key here would be not
> >combined improvement - it's only 50% to compare with single pair, but timing.
> >The idea is that spikes of activity will get 50% performance to compare with
> >mirrored pair.
> >
> >It's OLTP server and does not have prolonged acitvity periods, rather sharp
> >and frequent spikes.
> >Tempdb and logs will be on different disks in both situations, what would be
> >your choice?
>

When I configured what I thought was Raid 10 on a Perc3 controller using
Dell's windows raid disk management, I ended up with a span of mirrors.
However when I recreated it using the controller firmware/bios, I got a true
Raid 10. This was confirmed by the disk lights and seeing benchmarks
improve.
In the bios I could split each mirror over two channels of the controller.
The problem with the span was that the data filled up mirror 1 before
spilling into mirror 2 etc.

Your question interests me greatly as I think I have been too obsessed with
MB/sec instead of Iops. Our main server has Raid 10 arrays, some of 4 disks
and one of 8. I have set up a few filegroups and spread the data (clustered
and indexes) in what seemed to be a reasonable way. However we have so many
tables and usage patterns, it is getting to be impossible to optimise.

I am starting to wonder if I would be better off with a series of mirrors
(or 4 disk raid 10s) and just spread one filegroup across them all. This
would allow sql server to round robin the data across them all and reduce
contention for IOs.

Paul, i forgot you can create 1 filegroup using multiple disks. That way sql
will use them all for writing and reading. it's seems much better approach
than having 1 raid since iops of 2 raids by 2 disks almoust 1.8 times better
than 1 raid of 4 disks.
The only thing left now is test how I can restore db which uses 1 data file
to 3 data files residing on diff. raids.

"Paul Cahill" wrote:

> When I configured what I thought was Raid 10 on a Perc3 controller using
> Dell's windows raid disk management, I ended up with a span of mirrors.
> However when I recreated it using the controller firmware/bios, I got a true
> Raid 10. This was confirmed by the disk lights and seeing benchmarks
> improve.
> In the bios I could split each mirror over two channels of the controller.
> The problem with the span was that the data filled up mirror 1 before
> spilling into mirror 2 etc.
>
> Your question interests me greatly as I think I have been too obsessed with
> MB/sec instead of Iops. Our main server has Raid 10 arrays, some of 4 disks
> and one of 8. I have set up a few filegroups and spread the data (clustered
> and indexes) in what seemed to be a reasonable way. However we have so many
> tables and usage patterns, it is getting to be impossible to optimise.
>
> I am starting to wonder if I would be better off with a series of mirrors
> (or 4 disk raid 10s) and just spread one filegroup across them all. This
> would allow sql server to round robin the data across them all and reduce
> contention for IOs.
>
>
>
>

I don't think you can do it with a restore.

You may be able to add the two files to the filegroup and then issue a
shrink file to a size under what is currently being used on the original
file.
I'm not sure if this will move data to other files in the group. I know
shrink with emptyfile does. If is does work then the data may not be as
randomly distributed as starting with three files.

I guess after the restore you could add three files and then
shrink/empty/drop the original file. This could take a long time. I tried
this once on the logfile to move it while keeping the server running. I
found out the the initial file on the logfile filegroup is special in that
it cannot be emptied and dropped.

You could try this version of the sql io stress tester. I have only used the
old SQLIOStress utility

http://support.microsoft.com/kb/231619

Paul



"Gene." <Gene@discussions.microsoft.com> wrote in message
news:4B6DCD03-A73B-4244-A9AA-124343B0EE49@microsoft.com...
> Paul, i forgot you can create 1 filegroup using multiple disks. That way
> sql
> will use them all for writing and reading. it's seems much better approach
> than having 1 raid since iops of 2 raids by 2 disks almoust 1.8 times
> better
> than 1 raid of 4 disks.
> The only thing left now is test how I can restore db which uses 1 data
> file
> to 3 data files residing on diff. raids.
>
> "Paul Cahill" wrote:
>
>> When I configured what I thought was Raid 10 on a Perc3 controller using
>> Dell's windows raid disk management, I ended up with a span of mirrors.
>> However when I recreated it using the controller firmware/bios, I got a
>> true
>> Raid 10. This was confirmed by the disk lights and seeing benchmarks
>> improve.
>> In the bios I could split each mirror over two channels of the
>> controller.
>> The problem with the span was that the data filled up mirror 1 before
>> spilling into mirror 2 etc.
>>
>> Your question interests me greatly as I think I have been too obsessed
>> with
>> MB/sec instead of Iops. Our main server has Raid 10 arrays, some of 4
>> disks
>> and one of 8. I have set up a few filegroups and spread the data
>> (clustered
>> and indexes) in what seemed to be a reasonable way. However we have so
>> many
>> tables and usage patterns, it is getting to be impossible to optimise.
>>
>> I am starting to wonder if I would be better off with a series of mirrors
>> (or 4 disk raid 10s) and just spread one filegroup across them all. This
>> would allow sql server to round robin the data across them all and reduce
>> contention for IOs.
>>
>>
>>
>>