Table Partitioning on a RAID 5 Server

[justin.merth]

Is there any benefit in creating seperate file groups for a partitioned
table on a multi-processor server with RAID5 and 1 Logical Drive?

[rcamarda]

IMHO, No. I assume you have one raid controller and as you state, one
Logical Drive.
However, there might be a slight difference with windows if you have
two logical drives defined at the O/S level.

Rob.
ju**********@gmail.com wrote:

Is there any benefit in creating seperate file groups for a partitioned
table on a multi-processor server with RAID5 and 1 Logical Drive?

[justin.merth]

Does this hold true for creating mutiple filegroups for the database
itself? even if it is 100G + in size?

[Tony Rogerson]

RAID 5 should be no where near a database server now-a-days; use RAID 1 or
preferably RAID 10.

Each file spawns a thread out to the OS so yes there is benefit; you have to
be aware that the two threads will be doing async io so on a poor io
subsystem or one that is loaded you can actually hurt performance by doing
it.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ju**********@gmail.comwrote in message
news:11*********************@f1g2000cwa.googlegrou ps.com...

Is there any benefit in creating seperate file groups for a partitioned
table on a multi-processor server with RAID5 and 1 Logical Drive?

[rcamarda]

Tony,
I like to use RAID 10 when I can, much faster than 5.
Are you saying that even thou there is a single logical drive that the
raid controller built, there is a difference at the windows level when
we create seperate O/S logical drives? Or, are you refering back to the
Raid 5 vs. Raid 1??
I'd like to clarity on this point as I am going to building new
servers.
Tony Rogerson wrote:

RAID 5 should be no where near a database server now-a-days; use RAID 1 or
preferably RAID 10.

Each file spawns a thread out to the OS so yes there is benefit; you have to
be aware that the two threads will be doing async io so on a poor io
subsystem or one that is loaded you can actually hurt performance by doing
it.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ju**********@gmail.comwrote in message
news:11*********************@f1g2000cwa.googlegrou ps.com...

Is there any benefit in creating seperate file groups for a partitioned
table on a multi-processor server with RAID5 and 1 Logical Drive?

[Tony Rogerson]

If you create multiple files then a thread is spawned per file.

You really need to test to see if your system benefits from such a
configuration.

The other thing to think of with multiple files (not specifically file
groups because thats something different) is that each file has multiple
IAMS so you get less contention on object creation which is why there is a
recommendation to have a file per cpu on tempdb.

The other thing to think about is the system partition (usually C:) has a
default allocation size of 4KB which cannot be changed; the recommendation
is that when you format your own partition (so don't put the database/logs
on C:) that you match the allocation size (FORMAT /A) to the stripe size of
the RAID controller - usually you set it to 64KB - that's DELL's
recommendation for their equipment.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"rcamarda" <ro**************@gmail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Tony,
I like to use RAID 10 when I can, much faster than 5.
Are you saying that even thou there is a single logical drive that the
raid controller built, there is a difference at the windows level when
we create seperate O/S logical drives? Or, are you refering back to the
Raid 5 vs. Raid 1??
I'd like to clarity on this point as I am going to building new
servers.
Tony Rogerson wrote:

>RAID 5 should be no where near a database server now-a-days; use RAID 1
or
preferably RAID 10.

Each file spawns a thread out to the OS so yes there is benefit; you have
to
be aware that the two threads will be doing async io so on a poor io
subsystem or one that is loaded you can actually hurt performance by
doing
it.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ju**********@gmail.comwrote in message
news:11*********************@f1g2000cwa.googlegro ups.com...

Is there any benefit in creating seperate file groups for a partitioned
table on a multi-processor server with RAID5 and 1 Logical Drive?

[justin.merth]

Is there any "rule of thumb" for determining how many files a database
should be split into based on the size of the database or the number of
CPUs?

Thanks in advance for all of your advice

[DA Morgan]

ju**********@gmail.com wrote:

Is there any "rule of thumb" for determining how many files a database
should be split into based on the size of the database or the number of
CPUs?

Thanks in advance for all of your advice

No.

The point is to maximize the number of spindles. How you do that depends
on your hardware.

The only sure thing about a "rule of thumb" is that it is more often
wrong that right.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Tony Rogerson]

Rule of thumb for tempdb is one file per logical CPU.

Rule of thumb for application databases depends entirely on what you are
doing and the disk access pattern your queries create when they run - you
aim to service all queries from caching so its only the checkpoint /
lazywriter you need worry about.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ju**********@gmail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Is there any "rule of thumb" for determining how many files a database
should be split into based on the size of the database or the number of
CPUs?

Thanks in advance for all of your advice