RAID 10 vs. RAID 5 question

I have an external RAID with 10 36GB 15K drives. These drives are only for
the data of the SQL server, not tran logs, tempdb's etc.
Database Type is OLTP with more then 30% writes then reads
This is my question:
I understand more spindles are better, and I understand RAID 10 is faster
for writes then a RAID5 configuration. Plus both configurations will give me
enough HD space. So on with the question.
Should i use RAID 10 or RAID5?
RAID 10 would give be theoretically faster writes and great protection, but
gives me only 5 disks to write to simultaneously.
RAID 5 gives me around 9 disks but is known to be slower due to the
overhead.
Is the RAID5 really that much slower that it would hinder performance
compared to a RAID 10 with only 5 disks?
So what do you guys think?
-King
yes.
go RAID 1+0
If you have time you can test this with a handful of IOStress test
utilities.
you could also talk directly to your vendor.
As I learned today, you will also want to maximize the WRITE Cache on the
RAID Controller(s).
Cheers
Greg Jackson
PDX, Oregon
|||Hi
Where are the transaction logs going? They are as critical to the Db as the
data files.
RAID-10 wins hands down. RAID-5 has just too much overhead for the parity.
From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
RAID -10 can lose half it's drives, as long as it is never both pairs of a
mirror.
So performance wins.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
> I have an external RAID with 10 36GB 15K drives. These drives are only
for
> the data of the SQL server, not tran logs, tempdb's etc.
> Database Type is OLTP with more then 30% writes then reads
> This is my question:
> I understand more spindles are better, and I understand RAID 10 is faster
> for writes then a RAID5 configuration. Plus both configurations will give
me
> enough HD space. So on with the question.
> Should i use RAID 10 or RAID5?
> RAID 10 would give be theoretically faster writes and great protection,
but
> gives me only 5 disks to write to simultaneously.
> RAID 5 gives me around 9 disks but is known to be slower due to the
> overhead.
> Is the RAID5 really that much slower that it would hinder performance
> compared to a RAID 10 with only 5 disks?
> So what do you guys think?
>
> -King
>
|||Hi
Yes, have write cache, as long as the RAID card has battery backup. If not,
kiss your data goodbye as you will have some decent corruption.
For us, data security over performance, so write caching is off.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#62vk86EFHA.1264@.TK2MSFTNGP12.phx.gbl...
> yes.
> go RAID 1+0
> If you have time you can test this with a handful of IOStress test
> utilities.
> you could also talk directly to your vendor.
> As I learned today, you will also want to maximize the WRITE Cache on the
> RAID Controller(s).
>
> Cheers
> Greg Jackson
> PDX, Oregon
>
|||yes....HEAVENS YES.
One needs "battery backed" Cache.
GAJ
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23qs9yC7EFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Yes, have write cache, as long as the RAID card has battery backup. If
not,
> kiss your data goodbye as you will have some decent corruption.
>
Yes we have a 72-hour backup on all RAID controllers
[vbcol=seagreen]
> For us, data security over performance, so write caching is off.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:#62vk86EFHA.1264@.TK2MSFTNGP12.phx.gbl...
the
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e4EdmB7EFHA.4004@.tk2msftngp13.phx.gbl...
> Hi
> Where are the transaction logs going? They are as critical to the Db as
the
> data files.
> RAID-10 wins hands down. RAID-5 has just too much overhead for the parity.
> From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
> RAID -10 can lose half it's drives, as long as it is never both pairs of a
> mirror.
> So performance wins.
Mike,
Thanks! Thats what i thought.. but what puzzled me was the spindols. Since
the RAID5 configuration would have more drives to spread the write acrossed
then the RAID 1+0 i wasn't sure if it mattered. Keep in mind i haven't got
the hardware yet... so no testing has been done. and yes i will do testing.
thanks for your input.
-King
[vbcol=seagreen]
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
> news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
> for
faster[vbcol=seagreen]
give
> me
> but
>
|||In a Raid 1+0 you can't look at it as only x many drives to write to. In
your case you 10 drives that are configured as such. 5 Mirrored pairs that
are striped in a Raid 0 configuration. Yes that means you have to split the
data 5 ways vs. 9 for the Raid 5 but each split goes to a mirrored pair. The
mirrored pair has the option to read from one disk and write to the other,
write to both, read from both etc. It can be smart in how it reads and
writes to the mirrored pair. That plus the fact it doe not have to
calculate parity is a fast combination.
Andrew J. Kelly SQL MVP
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:%23qHC657EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:e4EdmB7EFHA.4004@.tk2msftngp13.phx.gbl...
> the
> Mike,
> Thanks! Thats what i thought.. but what puzzled me was the spindols.
> Since
> the RAID5 configuration would have more drives to spread the write
> acrossed
> then the RAID 1+0 i wasn't sure if it mattered. Keep in mind i haven't
> got
> the hardware yet... so no testing has been done. and yes i will do
> testing.
> thanks for your input.
> -King
>
> faster
> give
>
|||This website has great details and arguments why you should not use RAID 5
for a RDBMS implementation
http://www.baarf.com/
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
>I have an external RAID with 10 36GB 15K drives. These drives are only for
> the data of the SQL server, not tran logs, tempdb's etc.
> Database Type is OLTP with more then 30% writes then reads
> This is my question:
> I understand more spindles are better, and I understand RAID 10 is faster
> for writes then a RAID5 configuration. Plus both configurations will give
> me
> enough HD space. So on with the question.
> Should i use RAID 10 or RAID5?
> RAID 10 would give be theoretically faster writes and great protection,
> but
> gives me only 5 disks to write to simultaneously.
> RAID 5 gives me around 9 disks but is known to be slower due to the
> overhead.
> Is the RAID5 really that much slower that it would hinder performance
> compared to a RAID 10 with only 5 disks?
> So what do you guys think?
>
> -King
>

RAID 10 vs. RAID 5 question

I have an external RAID with 10 36GB 15K drives. These drives are only for
the data of the SQL server, not tran logs, tempdb's etc.
Database Type is OLTP with more then 30% writes then reads
This is my question:
I understand more spindles are better, and I understand RAID 10 is faster
for writes then a RAID5 configuration. Plus both configurations will give me
enough HD space. So on with the question.
Should i use RAID 10 or RAID5?
RAID 10 would give be theoretically faster writes and great protection, but
gives me only 5 disks to write to simultaneously.
RAID 5 gives me around 9 disks but is known to be slower due to the
overhead.
Is the RAID5 really that much slower that it would hinder performance
compared to a RAID 10 with only 5 disks?
So what do you guys think?
-Kingyes.
go RAID 1+0
If you have time you can test this with a handful of IOStress test
utilities.
you could also talk directly to your vendor.
As I learned today, you will also want to maximize the WRITE Cache on the
RAID Controller(s).
Cheers
Greg Jackson
PDX, Oregon|||Hi
Where are the transaction logs going? They are as critical to the Db as the
data files.
RAID-10 wins hands down. RAID-5 has just too much overhead for the parity.
From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
RAID -10 can lose half it's drives, as long as it is never both pairs of a
mirror.
So performance wins.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
> I have an external RAID with 10 36GB 15K drives. These drives are only
for
> the data of the SQL server, not tran logs, tempdb's etc.
> Database Type is OLTP with more then 30% writes then reads
> This is my question:
> I understand more spindles are better, and I understand RAID 10 is faster
> for writes then a RAID5 configuration. Plus both configurations will give
me
> enough HD space. So on with the question.
> Should i use RAID 10 or RAID5?
> RAID 10 would give be theoretically faster writes and great protection,
but
> gives me only 5 disks to write to simultaneously.
> RAID 5 gives me around 9 disks but is known to be slower due to the
> overhead.
> Is the RAID5 really that much slower that it would hinder performance
> compared to a RAID 10 with only 5 disks?
> So what do you guys think?
>
> -King
>|||Hi
Yes, have write cache, as long as the RAID card has battery backup. If not,
kiss your data goodbye as you will have some decent corruption.
For us, data security over performance, so write caching is off.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#62vk86EFHA.1264@.TK2MSFTNGP12.phx.gbl...
> yes.
> go RAID 1+0
> If you have time you can test this with a handful of IOStress test
> utilities.
> you could also talk directly to your vendor.
> As I learned today, you will also want to maximize the WRITE Cache on the
> RAID Controller(s).
>
> Cheers
> Greg Jackson
> PDX, Oregon
>|||yes....HEAVENS YES.
One needs "battery backed" Cache.
GAJ|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23qs9yC7EFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Yes, have write cache, as long as the RAID card has battery backup. If
not,
> kiss your data goodbye as you will have some decent corruption.
>
Yes we have a 72-hour backup on all RAID controllers

> For us, data security over performance, so write caching is off.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:#62vk86EFHA.1264@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e4EdmB7EFHA.4004@.tk2msftngp13.phx.gbl...
> Hi
> Where are the transaction logs going? They are as critical to the Db as
the
> data files.
> RAID-10 wins hands down. RAID-5 has just too much overhead for the parity.
> From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
> RAID -10 can lose half it's drives, as long as it is never both pairs of a
> mirror.
> So performance wins.
Mike,
Thanks! Thats what i thought.. but what puzzled me was the spindols. Since
the RAID5 configuration would have more drives to spread the write acrossed
then the RAID 1+0 i wasn't sure if it mattered. Keep in mind i haven't got
the hardware yet... so no testing has been done. and yes i will do testing.
thanks for your input.
-King

> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
> news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
> for
faster[vbcol=seagreen]
give[vbcol=seagreen]
> me
> but
>|||In a Raid 1+0 you can't look at it as only x many drives to write to. In
your case you 10 drives that are configured as such. 5 Mirrored pairs that
are striped in a Raid 0 configuration. Yes that means you have to split the
data 5 ways vs. 9 for the Raid 5 but each split goes to a mirrored pair. The
mirrored pair has the option to read from one disk and write to the other,
write to both, read from both etc. It can be smart in how it reads and
writes to the mirrored pair. That plus the fact it doe not have to
calculate parity is a fast combination.
Andrew J. Kelly SQL MVP
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:%23qHC657EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:e4EdmB7EFHA.4004@.tk2msftngp13.phx.gbl...
> the
> Mike,
> Thanks! Thats what i thought.. but what puzzled me was the spindols.
> Since
> the RAID5 configuration would have more drives to spread the write
> acrossed
> then the RAID 1+0 i wasn't sure if it mattered. Keep in mind i haven't
> got
> the hardware yet... so no testing has been done. and yes i will do
> testing.
> thanks for your input.
> -King
>
> faster
> give
>|||This website has great details and arguments why you should not use RAID 5
for a RDBMS implementation
http://www.baarf.com/
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
>I have an external RAID with 10 36GB 15K drives. These drives are only for
> the data of the SQL server, not tran logs, tempdb's etc.
> Database Type is OLTP with more then 30% writes then reads
> This is my question:
> I understand more spindles are better, and I understand RAID 10 is faster
> for writes then a RAID5 configuration. Plus both configurations will give
> me
> enough HD space. So on with the question.
> Should i use RAID 10 or RAID5?
> RAID 10 would give be theoretically faster writes and great protection,
> but
> gives me only 5 disks to write to simultaneously.
> RAID 5 gives me around 9 disks but is known to be slower due to the
> overhead.
> Is the RAID5 really that much slower that it would hinder performance
> compared to a RAID 10 with only 5 disks?
> So what do you guys think?
>
> -King
>

RAID 10 vs. RAID 5 question

I have an external RAID with 10 36GB 15K drives. These drives are only for
the data of the SQL server, not tran logs, tempdb's etc.
Database Type is OLTP with more then 30% writes then reads
This is my question:
I understand more spindles are better, and I understand RAID 10 is faster
for writes then a RAID5 configuration. Plus both configurations will give me
enough HD space. So on with the question.
Should i use RAID 10 or RAID5?
RAID 10 would give be theoretically faster writes and great protection, but
gives me only 5 disks to write to simultaneously.
RAID 5 gives me around 9 disks but is known to be slower due to the
overhead.
Is the RAID5 really that much slower that it would hinder performance
compared to a RAID 10 with only 5 disks?
So what do you guys think?
-Kingyes.
go RAID 1+0
If you have time you can test this with a handful of IOStress test
utilities.
you could also talk directly to your vendor.
As I learned today, you will also want to maximize the WRITE Cache on the
RAID Controller(s).
Cheers
Greg Jackson
PDX, Oregon|||Hi
Where are the transaction logs going? They are as critical to the Db as the
data files.
RAID-10 wins hands down. RAID-5 has just too much overhead for the parity.
From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
RAID -10 can lose half it's drives, as long as it is never both pairs of a
mirror.
So performance wins.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
> I have an external RAID with 10 36GB 15K drives. These drives are only
for
> the data of the SQL server, not tran logs, tempdb's etc.
> Database Type is OLTP with more then 30% writes then reads
> This is my question:
> I understand more spindles are better, and I understand RAID 10 is faster
> for writes then a RAID5 configuration. Plus both configurations will give
me
> enough HD space. So on with the question.
> Should i use RAID 10 or RAID5?
> RAID 10 would give be theoretically faster writes and great protection,
but
> gives me only 5 disks to write to simultaneously.
> RAID 5 gives me around 9 disks but is known to be slower due to the
> overhead.
> Is the RAID5 really that much slower that it would hinder performance
> compared to a RAID 10 with only 5 disks?
> So what do you guys think?
>
> -King
>|||Hi
Yes, have write cache, as long as the RAID card has battery backup. If not,
kiss your data goodbye as you will have some decent corruption.
For us, data security over performance, so write caching is off.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#62vk86EFHA.1264@.TK2MSFTNGP12.phx.gbl...
> yes.
> go RAID 1+0
> If you have time you can test this with a handful of IOStress test
> utilities.
> you could also talk directly to your vendor.
> As I learned today, you will also want to maximize the WRITE Cache on the
> RAID Controller(s).
>
> Cheers
> Greg Jackson
> PDX, Oregon
>|||yes....HEAVENS YES.
One needs "battery backed" Cache.
GAJ|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23qs9yC7EFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Yes, have write cache, as long as the RAID card has battery backup. If
not,
> kiss your data goodbye as you will have some decent corruption.
>
Yes we have a 72-hour backup on all RAID controllers
> For us, data security over performance, so write caching is off.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:#62vk86EFHA.1264@.TK2MSFTNGP12.phx.gbl...
> > yes.
> >
> > go RAID 1+0
> >
> > If you have time you can test this with a handful of IOStress test
> > utilities.
> >
> > you could also talk directly to your vendor.
> >
> > As I learned today, you will also want to maximize the WRITE Cache on
the
> > RAID Controller(s).
> >
> >
> > Cheers
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e4EdmB7EFHA.4004@.tk2msftngp13.phx.gbl...
> Hi
> Where are the transaction logs going? They are as critical to the Db as
the
> data files.
> RAID-10 wins hands down. RAID-5 has just too much overhead for the parity.
> From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
> RAID -10 can lose half it's drives, as long as it is never both pairs of a
> mirror.
> So performance wins.
Mike,
Thanks! Thats what i thought.. but what puzzled me was the spindols. Since
the RAID5 configuration would have more drives to spread the write acrossed
then the RAID 1+0 i wasn't sure if it mattered. Keep in mind i haven't got
the hardware yet... so no testing has been done. and yes i will do testing.
thanks for your input.
-King
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
> news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
> > I have an external RAID with 10 36GB 15K drives. These drives are only
> for
> > the data of the SQL server, not tran logs, tempdb's etc.
> >
> > Database Type is OLTP with more then 30% writes then reads
> >
> > This is my question:
> >
> > I understand more spindles are better, and I understand RAID 10 is
faster
> > for writes then a RAID5 configuration. Plus both configurations will
give
> me
> > enough HD space. So on with the question.
> >
> > Should i use RAID 10 or RAID5?
> >
> > RAID 10 would give be theoretically faster writes and great protection,
> but
> > gives me only 5 disks to write to simultaneously.
> >
> > RAID 5 gives me around 9 disks but is known to be slower due to the
> > overhead.
> >
> > Is the RAID5 really that much slower that it would hinder performance
> > compared to a RAID 10 with only 5 disks?
> >
> > So what do you guys think?
> >
> >
> > -King
> >
> >
>|||In a Raid 1+0 you can't look at it as only x many drives to write to. In
your case you 10 drives that are configured as such. 5 Mirrored pairs that
are striped in a Raid 0 configuration. Yes that means you have to split the
data 5 ways vs. 9 for the Raid 5 but each split goes to a mirrored pair. The
mirrored pair has the option to read from one disk and write to the other,
write to both, read from both etc. It can be smart in how it reads and
writes to the mirrored pair. That plus the fact it doe not have to
calculate parity is a fast combination.
--
Andrew J. Kelly SQL MVP
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:%23qHC657EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:e4EdmB7EFHA.4004@.tk2msftngp13.phx.gbl...
>> Hi
>> Where are the transaction logs going? They are as critical to the Db as
> the
>> data files.
>> RAID-10 wins hands down. RAID-5 has just too much overhead for the
>> parity.
>> From a safety perspective, on RAID-5, you loose 2 drives, bye bye data.
>> RAID -10 can lose half it's drives, as long as it is never both pairs of
>> a
>> mirror.
>> So performance wins.
> Mike,
> Thanks! Thats what i thought.. but what puzzled me was the spindols.
> Since
> the RAID5 configuration would have more drives to spread the write
> acrossed
> then the RAID 1+0 i wasn't sure if it mattered. Keep in mind i haven't
> got
> the hardware yet... so no testing has been done. and yes i will do
> testing.
> thanks for your input.
> -King
>
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
>> news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
>> > I have an external RAID with 10 36GB 15K drives. These drives are only
>> for
>> > the data of the SQL server, not tran logs, tempdb's etc.
>> >
>> > Database Type is OLTP with more then 30% writes then reads
>> >
>> > This is my question:
>> >
>> > I understand more spindles are better, and I understand RAID 10 is
> faster
>> > for writes then a RAID5 configuration. Plus both configurations will
> give
>> me
>> > enough HD space. So on with the question.
>> >
>> > Should i use RAID 10 or RAID5?
>> >
>> > RAID 10 would give be theoretically faster writes and great protection,
>> but
>> > gives me only 5 disks to write to simultaneously.
>> >
>> > RAID 5 gives me around 9 disks but is known to be slower due to the
>> > overhead.
>> >
>> > Is the RAID5 really that much slower that it would hinder performance
>> > compared to a RAID 10 with only 5 disks?
>> >
>> > So what do you guys think?
>> >
>> >
>> > -King
>> >
>> >
>>
>|||This website has great details and arguments why you should not use RAID 5
for a RDBMS implementation
http://www.baarf.com/
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"news.microsoft.com" <richk@.bluestreammedia.com> wrote in message
news:OUGbB56EFHA.2176@.TK2MSFTNGP15.phx.gbl...
>I have an external RAID with 10 36GB 15K drives. These drives are only for
> the data of the SQL server, not tran logs, tempdb's etc.
> Database Type is OLTP with more then 30% writes then reads
> This is my question:
> I understand more spindles are better, and I understand RAID 10 is faster
> for writes then a RAID5 configuration. Plus both configurations will give
> me
> enough HD space. So on with the question.
> Should i use RAID 10 or RAID5?
> RAID 10 would give be theoretically faster writes and great protection,
> but
> gives me only 5 disks to write to simultaneously.
> RAID 5 gives me around 9 disks but is known to be slower due to the
> overhead.
> Is the RAID5 really that much slower that it would hinder performance
> compared to a RAID 10 with only 5 disks?
> So what do you guys think?
>
> -King
>sql

RAID - config Question

hard to really answer but here is the question
Your opinions please:
I have 6 15k 72GB Drives in an ARRAY.
This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
Etc)
All I do on this box is copy mass amounts of data from DB A to DB B. This is
dev platform only not a production environment.
Generally speaking, I know that placing Logs and TempDB on one Volume and
DATA\Indexes on another Volume is preferable.
However, My thoughts are this.....
If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
If I use 1 Volume, I'm not getting the Seperation that we would typically
want in a production OLTP Environment. However, with 1 single volume, I will
have 6 disks to stripe across....
If data copy performance was your main objective, which would likely provide
best performance ?"
A) 2 volumes with 3 disks each
B) 1 volume with all 6 disks
This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
Read/Write Cache (Configured 50/50)
Thanks in advance
Greg Jackson
Portland, OR
GAJ
pdxJaxon wrote:
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B.
> This is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume
> and DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
> really that great for Striping (I'm using RAID 0 As I need no Fault
> Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would
> typically want in a production OLTP Environment. However, with 1
> single volume, I will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery
> Backed Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
Using only striping, I might consider a simple 2 disk array for t-logs
and tempdb and a 4disk array for the data. But it depends on how much
data you're inserting/updating. If the t-log is extremely active use 2
3-disk arrays.
Where is the OS located?
David Gugick
Imceda Software
www.imceda.com
|||o.s. will be with Tlogs and tempdb
Currently I have 3 and 3 and my DTS jobs are absolutely PEGGING the IO on my
TempDB Volume.
to be expected as I'm moving 10's of millions of records. BUT, if I can
improve by reconfiguring and speed this up, I can reduce the cost of the
effort here.
(taking 8+ hours now. If I can increase performance by 10% it is a
significant savings)
GAJ
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23igp2gsEFHA.3032@.TK2MSFTNGP12.phx.gbl...
> pdxJaxon wrote:
> Using only striping, I might consider a simple 2 disk array for t-logs and
> tempdb and a 4disk array for the data. But it depends on how much data
> you're inserting/updating. If the t-log is extremely active use 2 3-disk
> arrays.
> Where is the OS located?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||By volume I hope you mean array and not simply a logical device. If fault
tolerance is not an issue then why not have one disk for the OS and the Log
files (both tempdb and the user dbs). Then either use the other 5 for the
data files or take one or two for tempdb and the others for the user data
files. You absolutely need to separate the logs from the data files. If
you have that much tempdb you may want to split that data file out as well
but only testing will tell for sure. In either case change the caching on
the disk controller to be 100% write back and you should see a big
improvement as well. 8 hours to process only a few 10's of millions of rows
is pretty bad.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This
> is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I
> will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>
|||actually I misspoke.
the entire job is moving a more than 10s of millions of records (~60GB).
I have individual tables with 10s of millions of records.
Not the biggest db I"ve ever played with, but it's non-trivial.
By Volume I mean an array.
I have 2 arrays with 3 disks each Both RAID 0.
On array 1 I have C: (OS) and D: (Logs and TempDB)
On Array 2, I have E: (Data and Indexes)
You think making the Cache 100% writes will help ?
I am reading from DB A and Writing to DB B .....
If so, that is an easy change.
GAJ
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> By volume I hope you mean array and not simply a logical device. If fault
> tolerance is not an issue then why not have one disk for the OS and the
> Log files (both tempdb and the user dbs). Then either use the other 5 for
> the data files or take one or two for tempdb and the others for the user
> data files. You absolutely need to separate the logs from the data files.
> If you have that much tempdb you may want to split that data file out as
> well but only testing will tell for sure. In either case change the
> caching on the disk controller to be 100% write back and you should see a
> big improvement as well. 8 hours to process only a few 10's of millions of
> rows is pretty bad.
> --
> Andrew J. Kelly SQL MVP
>
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
>
|||pdxJaxon wrote:
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records
> (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
If the databases are on the same server, I might try the following:
Array 1 - OS + TempDB (2 drives) - only writes
Array 2 - Database 1 - only reads
Array 3 - Database 2 - only writes
Make sure your clustered indexes are not causing the data to insert out
of order. If so, consider dropping the clustered indexes before the load
or redesign to a key that won't cause page splitting.
David Gugick
Imceda Software
www.imceda.com
|||Hi,
I think Andrew really nailed it.
This is might be a good opportunity to tweak the IoPageLockLimit registry
setting on your server. I believe Windows restricts the amount of RAM that
can be locked for file system operations to 512 kb. I would play around with
this setting and see if you can gain any performance...
Also, your G4 has at least 1 MB of L2 cache (depending how many CPUs you
have, it could be up to 2 MB). Windows is optimized for 256 KB so you might
want to check the SecondLevelDataCache setting.
If your business still requires better performance after all the tips given
by the previous people, you could invest 500$ to get a second array
controller (such as the Smart Array 641)...
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"pdxJaxon" wrote:

> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
> Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This is
> dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I will
> have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely provide
> best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>
>
|||thanks a ton.
I'm gonna look into these settings
GAJ
|||SQL Server does not need read cache on the controller since it does it's own
read ahead caching anyway and will usually do a better job at predicting
what it will need. You are doing massive writes and your disks probably
can't handle the load by them selves so going 100% write cache will help a
ton. Since you are doing so much tempdb and log activity you want to make
sure the tempdb is not on the same disk as the logs.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:udwH1ftEFHA.960@.TK2MSFTNGP09.phx.gbl...
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
> GAJ
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks,
I'm gonna light this up Today...!
GJ

RAID - config Question

hard to really answer but here is the question
Your opinions please:
I have 6 15k 72GB Drives in an ARRAY.
This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
Etc)
All I do on this box is copy mass amounts of data from DB A to DB B. This is
dev platform only not a production environment.
Generally speaking, I know that placing Logs and TempDB on one Volume and
DATA\Indexes on another Volume is preferable.
However, My thoughts are this.....
If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
If I use 1 Volume, I'm not getting the Seperation that we would typically
want in a production OLTP Environment. However, with 1 single volume, I will
have 6 disks to stripe across....
If data copy performance was your main objective, which would likely provide
best performance ?"
A) 2 volumes with 3 disks each
B) 1 volume with all 6 disks
This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
Read/Write Cache (Configured 50/50)
Thanks in advance
Greg Jackson
Portland, OR
GAJpdxJaxon wrote:
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B.
> This is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume
> and DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
> really that great for Striping (I'm using RAID 0 As I need no Fault
> Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would
> typically want in a production OLTP Environment. However, with 1
> single volume, I will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery
> Backed Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
Using only striping, I might consider a simple 2 disk array for t-logs
and tempdb and a 4disk array for the data. But it depends on how much
data you're inserting/updating. If the t-log is extremely active use 2
3-disk arrays.
Where is the OS located?
David Gugick
Imceda Software
www.imceda.com|||o.s. will be with Tlogs and tempdb
Currently I have 3 and 3 and my DTS jobs are absolutely PEGGING the IO on my
TempDB Volume.
to be expected as I'm moving 10's of millions of records. BUT, if I can
improve by reconfiguring and speed this up, I can reduce the cost of the
effort here.
(taking 8+ hours now. If I can increase performance by 10% it is a
significant savings)
GAJ
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23igp2gsEFHA.3032@.TK2MSFTNGP12.phx.gbl...
> pdxJaxon wrote:
> Using only striping, I might consider a simple 2 disk array for t-logs and
> tempdb and a 4disk array for the data. But it depends on how much data
> you're inserting/updating. If the t-log is extremely active use 2 3-disk
> arrays.
> Where is the OS located?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||By volume I hope you mean array and not simply a logical device. If fault
tolerance is not an issue then why not have one disk for the OS and the Log
files (both tempdb and the user dbs). Then either use the other 5 for the
data files or take one or two for tempdb and the others for the user data
files. You absolutely need to separate the logs from the data files. If
you have that much tempdb you may want to split that data file out as well
but only testing will tell for sure. In either case change the caching on
the disk controller to be 100% write back and you should see a big
improvement as well. 8 hours to process only a few 10's of millions of rows
is pretty bad.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This
> is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I
> will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>|||actually I misspoke.
the entire job is moving a more than 10s of millions of records (~60GB).
I have individual tables with 10s of millions of records.
Not the biggest db I"ve ever played with, but it's non-trivial.
By Volume I mean an array.
I have 2 arrays with 3 disks each Both RAID 0.
On array 1 I have C: (OS) and D: (Logs and TempDB)
On Array 2, I have E: (Data and Indexes)
You think making the Cache 100% writes will help ?
I am reading from DB A and Writing to DB B .....
If so, that is an easy change.
GAJ
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> By volume I hope you mean array and not simply a logical device. If fault
> tolerance is not an issue then why not have one disk for the OS and the
> Log files (both tempdb and the user dbs). Then either use the other 5 for
> the data files or take one or two for tempdb and the others for the user
> data files. You absolutely need to separate the logs from the data files.
> If you have that much tempdb you may want to split that data file out as
> well but only testing will tell for sure. In either case change the
> caching on the disk controller to be 100% write back and you should see a
> big improvement as well. 8 hours to process only a few 10's of millions of
> rows is pretty bad.
> --
> Andrew J. Kelly SQL MVP
>
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
>|||pdxJaxon wrote:
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records
> (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
If the databases are on the same server, I might try the following:
Array 1 - OS + TempDB (2 drives) - only writes
Array 2 - Database 1 - only reads
Array 3 - Database 2 - only writes
Make sure your clustered indexes are not causing the data to insert out
of order. If so, consider dropping the clustered indexes before the load
or redesign to a key that won't cause page splitting.
David Gugick
Imceda Software
www.imceda.com|||Hi,
I think Andrew really nailed it.
This is might be a good opportunity to tweak the IoPageLockLimit registry
setting on your server. I believe Windows restricts the amount of RAM that
can be locked for file system operations to 512 kb. I would play around with
this setting and see if you can gain any performance...
Also, your G4 has at least 1 MB of L2 cache (depending how many CPUs you
have, it could be up to 2 MB). Windows is optimized for 256 KB so you might
want to check the SecondLevelDataCache setting.
If your business still requires better performance after all the tips given
by the previous people, you could invest 500$ to get a second array
controller (such as the Smart Array 641)...
--
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"pdxJaxon" wrote:

> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLT
P,
> Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This
is
> dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I wi
ll
> have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely provi
de
> best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>
>|||thanks a ton.
I'm gonna look into these settings
GAJ|||SQL Server does not need read cache on the controller since it does it's own
read ahead caching anyway and will usually do a better job at predicting
what it will need. You are doing massive writes and your disks probably
can't handle the load by them selves so going 100% write cache will help a
ton. Since you are doing so much tempdb and log activity you want to make
sure the tempdb is not on the same disk as the logs.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:udwH1ftEFHA.960@.TK2MSFTNGP09.phx.gbl...
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
> GAJ
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
>|||Thanks,
I'm gonna light this up Today...!
GJsql

RAID - config Question

hard to really answer but here is the question
Your opinions please:
I have 6 15k 72GB Drives in an ARRAY.
This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
Etc)
All I do on this box is copy mass amounts of data from DB A to DB B. This is
dev platform only not a production environment.
Generally speaking, I know that placing Logs and TempDB on one Volume and
DATA\Indexes on another Volume is preferable.
However, My thoughts are this.....
If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
If I use 1 Volume, I'm not getting the Seperation that we would typically
want in a production OLTP Environment. However, with 1 single volume, I will
have 6 disks to stripe across....
If data copy performance was your main objective, which would likely provide
best performance ?"
A) 2 volumes with 3 disks each
B) 1 volume with all 6 disks
This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
Read/Write Cache (Configured 50/50)
Thanks in advance
Greg Jackson
Portland, OR
GAJpdxJaxon wrote:
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B.
> This is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume
> and DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
> really that great for Striping (I'm using RAID 0 As I need no Fault
> Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would
> typically want in a production OLTP Environment. However, with 1
> single volume, I will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery
> Backed Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
Using only striping, I might consider a simple 2 disk array for t-logs
and tempdb and a 4disk array for the data. But it depends on how much
data you're inserting/updating. If the t-log is extremely active use 2
3-disk arrays.
Where is the OS located?
David Gugick
Imceda Software
www.imceda.com|||o.s. will be with Tlogs and tempdb
Currently I have 3 and 3 and my DTS jobs are absolutely PEGGING the IO on my
TempDB Volume.
to be expected as I'm moving 10's of millions of records. BUT, if I can
improve by reconfiguring and speed this up, I can reduce the cost of the
effort here.
(taking 8+ hours now. If I can increase performance by 10% it is a
significant savings)
GAJ
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23igp2gsEFHA.3032@.TK2MSFTNGP12.phx.gbl...
> pdxJaxon wrote:
>> hard to really answer but here is the question
>> Your opinions please:
>> I have 6 15k 72GB Drives in an ARRAY.
>> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
>> OLTP, Etc)
>> All I do on this box is copy mass amounts of data from DB A to DB B.
>> This is dev platform only not a production environment.
>> Generally speaking, I know that placing Logs and TempDB on one Volume
>> and DATA\Indexes on another Volume is preferable.
>> However, My thoughts are this.....
>> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
>> really that great for Striping (I'm using RAID 0 As I need no Fault
>> Tolerance)
>> If I use 1 Volume, I'm not getting the Seperation that we would
>> typically want in a production OLTP Environment. However, with 1
>> single volume, I will have 6 disks to stripe across....
>> If data copy performance was your main objective, which would likely
>> provide best performance ?"
>> A) 2 volumes with 3 disks each
>> B) 1 volume with all 6 disks
>> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery
>> Backed Read/Write Cache (Configured 50/50)
>> Thanks in advance
>> Greg Jackson
>> Portland, OR
>> GAJ
> Using only striping, I might consider a simple 2 disk array for t-logs and
> tempdb and a 4disk array for the data. But it depends on how much data
> you're inserting/updating. If the t-log is extremely active use 2 3-disk
> arrays.
> Where is the OS located?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||By volume I hope you mean array and not simply a logical device. If fault
tolerance is not an issue then why not have one disk for the OS and the Log
files (both tempdb and the user dbs). Then either use the other 5 for the
data files or take one or two for tempdb and the others for the user data
files. You absolutely need to separate the logs from the data files. If
you have that much tempdb you may want to split that data file out as well
but only testing will tell for sure. In either case change the caching on
the disk controller to be 100% write back and you should see a big
improvement as well. 8 hours to process only a few 10's of millions of rows
is pretty bad.
--
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This
> is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I
> will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>|||actually I misspoke.
the entire job is moving a more than 10s of millions of records (~60GB).
I have individual tables with 10s of millions of records.
Not the biggest db I"ve ever played with, but it's non-trivial.
By Volume I mean an array.
I have 2 arrays with 3 disks each Both RAID 0.
On array 1 I have C: (OS) and D: (Logs and TempDB)
On Array 2, I have E: (Data and Indexes)
You think making the Cache 100% writes will help ?
I am reading from DB A and Writing to DB B .....
If so, that is an easy change.
GAJ
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> By volume I hope you mean array and not simply a logical device. If fault
> tolerance is not an issue then why not have one disk for the OS and the
> Log files (both tempdb and the user dbs). Then either use the other 5 for
> the data files or take one or two for tempdb and the others for the user
> data files. You absolutely need to separate the logs from the data files.
> If you have that much tempdb you may want to split that data file out as
> well but only testing will tell for sure. In either case change the
> caching on the disk controller to be 100% write back and you should see a
> big improvement as well. 8 hours to process only a few 10's of millions of
> rows is pretty bad.
> --
> Andrew J. Kelly SQL MVP
>
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
>> hard to really answer but here is the question
>> Your opinions please:
>> I have 6 15k 72GB Drives in an ARRAY.
>> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
>> OLTP, Etc)
>> All I do on this box is copy mass amounts of data from DB A to DB B. This
>> is dev platform only not a production environment.
>> Generally speaking, I know that placing Logs and TempDB on one Volume and
>> DATA\Indexes on another Volume is preferable.
>> However, My thoughts are this.....
>> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
>> really that great for Striping (I'm using RAID 0 As I need no Fault
>> Tolerance)
>> If I use 1 Volume, I'm not getting the Seperation that we would typically
>> want in a production OLTP Environment. However, with 1 single volume, I
>> will have 6 disks to stripe across....
>> If data copy performance was your main objective, which would likely
>> provide best performance ?"
>> A) 2 volumes with 3 disks each
>> B) 1 volume with all 6 disks
>> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
>> Read/Write Cache (Configured 50/50)
>> Thanks in advance
>> Greg Jackson
>> Portland, OR
>> GAJ
>|||pdxJaxon wrote:
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records
> (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
If the databases are on the same server, I might try the following:
Array 1 - OS + TempDB (2 drives) - only writes
Array 2 - Database 1 - only reads
Array 3 - Database 2 - only writes
Make sure your clustered indexes are not causing the data to insert out
of order. If so, consider dropping the clustered indexes before the load
or redesign to a key that won't cause page splitting.
David Gugick
Imceda Software
www.imceda.com|||Hi,
I think Andrew really nailed it.
This is might be a good opportunity to tweak the IoPageLockLimit registry
setting on your server. I believe Windows restricts the amount of RAM that
can be locked for file system operations to 512 kb. I would play around with
this setting and see if you can gain any performance...
Also, your G4 has at least 1 MB of L2 cache (depending how many CPUs you
have, it could be up to 2 MB). Windows is optimized for 256 KB so you might
want to check the SecondLevelDataCache setting.
If your business still requires better performance after all the tips given
by the previous people, you could invest 500$ to get a second array
controller (such as the Smart Array 641)...
--
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"pdxJaxon" wrote:
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
> Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This is
> dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I will
> have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely provide
> best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>
>|||thanks a ton.
I'm gonna look into these settings
GAJ|||SQL Server does not need read cache on the controller since it does it's own
read ahead caching anyway and will usually do a better job at predicting
what it will need. You are doing massive writes and your disks probably
can't handle the load by them selves so going 100% write cache will help a
ton. Since you are doing so much tempdb and log activity you want to make
sure the tempdb is not on the same disk as the logs.
--
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:udwH1ftEFHA.960@.TK2MSFTNGP09.phx.gbl...
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
> GAJ
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
>> By volume I hope you mean array and not simply a logical device. If
>> fault tolerance is not an issue then why not have one disk for the OS and
>> the Log files (both tempdb and the user dbs). Then either use the other 5
>> for the data files or take one or two for tempdb and the others for the
>> user data files. You absolutely need to separate the logs from the data
>> files. If you have that much tempdb you may want to split that data file
>> out as well but only testing will tell for sure. In either case change
>> the caching on the disk controller to be 100% write back and you should
>> see a big improvement as well. 8 hours to process only a few 10's of
>> millions of rows is pretty bad.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
>> news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
>> hard to really answer but here is the question
>> Your opinions please:
>> I have 6 15k 72GB Drives in an ARRAY.
>> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
>> OLTP, Etc)
>> All I do on this box is copy mass amounts of data from DB A to DB B.
>> This is dev platform only not a production environment.
>> Generally speaking, I know that placing Logs and TempDB on one Volume
>> and DATA\Indexes on another Volume is preferable.
>> However, My thoughts are this.....
>> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
>> really that great for Striping (I'm using RAID 0 As I need no Fault
>> Tolerance)
>> If I use 1 Volume, I'm not getting the Seperation that we would
>> typically want in a production OLTP Environment. However, with 1 single
>> volume, I will have 6 disks to stripe across....
>> If data copy performance was your main objective, which would likely
>> provide best performance ?"
>> A) 2 volumes with 3 disks each
>> B) 1 volume with all 6 disks
>> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
>> Read/Write Cache (Configured 50/50)
>> Thanks in advance
>> Greg Jackson
>> Portland, OR
>> GAJ
>>
>|||Thanks,
I'm gonna light this up Today...!
GJ