Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?
The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>
|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>
|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:

> What exactly are these "I/O" devices? Are these in addition to the device
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
>
>

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:

> What exactly are these "I/O" devices? Are these in addition to the devic
e
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
>
>