Optimal Drive Performance

I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus|||Andrew J. Kelly wrote:
> Memory is too cheap these days to not have enough. How large do you expect
> your DB to get and of that how much of the data would be read or written to
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||To try and cover your issues separately
Memory - as suggested, memory is relatively inexpensive these days and database servers are generally pretty memory and disk intensive. It really depends on the usage profile and DB size but 4GB is not really *that* expensive
Disk config: Disk config is very important as it can make a major difference to the performance of the database
Here are some general recommendations but again this depends on the app and how it will be used
RAID 5: Good fault tolerance, good read performance, BAD write performance (parity has to be written every time the disk is written to). Use this if the DB is read intensive with few writes. RAID 5 offers good fault tolerance at a relatively low cost
RAID 1: Good fault tolerance, and good performance for sequential writes (such as transaction logs). Expensive because you only have effective use of half the disk
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent performance, but NO fault tolerance
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example if you have 8 disks, it would stripe data across 4 mirrors. This offers excellent performance, excellent fault tolerance but a not-so-excellent bank balance
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, you would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirrored. This also offers good performance and fault tolerance (as RAID 1+0) but this will be degraded in the event of disk failures (much more than RAID 1+0)
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always choose RAID 1+0
An example of a high-end disk spec would be
OS: 2 x 18.2GB RAID
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of 2, the total number constrained by the storage device
This may or may not be an overkill depending on the actual app
In terms of controllers, separate controllers sounds like a good idea - again its down to the cost/benefit of doing this
I hope this helps as a very general guideline
Regards
Ro