Multiple files in FileGroup on RAID

We are putting out DBs on a RAID array and have the data and indexes in
their own file groups. My question is where can I find information about any
performance gain in I/O based on the number of files I have in the
FileGroup.
The array is made up of 14 drives with 1 hot spare (RAID 5). How many
files?
Thx"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
>
Spreading out the data to multiple files in a filegroup on top of a single
RAID 5 array is redundant. Each file will be spread out over all 14 drives
by the RAID striping to begin with. It's useless do do it again with
multiple files per filegroup.
In your setup you needn't even bother putting data and indexes into seperate
filegroups.
If you had your disks RAIDed into 7 seperate 2 drive RAID 1 mirrors, then
you might spread a filegroup over multiple volumes for performance and
capacity.
But a better configuration might be to bundle the mirrored sets into 4-drive
stripe and mirror sets.
David|||While there can be performance gains with having multiple files per file
group there is no chart etc. that exists to show what your after. The gains
are going to be greatest with lots of drive arrays and lots of processors
when you have multiple files. Chances are you will not max out the
capabilities of the way you have it now with that hardware setup.
--
Andrew J. Kelly
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
> Thx
>

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?
Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>
|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]
|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > Is there an advantage (besides backup and restore) of creating multiple
> > datafiles on multiple disks on one physical raid set for a large database
> (>
> > 200GB) ?
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
> > Geert
> > Performance for example if you query a huge table /s that are on
separate
> > physical discks SQL Server will create two threads to retrieve the data
> > which means less I/O .
> >
> >
> > "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> > news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > > Is there an advantage (besides backup and restore) of creating
multiple
> > > datafiles on multiple disks on one physical raid set for a large
database
> > (>
> > > 200GB) ?
> >
> >
> >|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
--
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
>
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

Multiple data files in a filegroup

I have a storage subsystem which allows five independant RAID 10 devices. I am creating five filegroups each with 2 data files distributed across the devices in a smart round robin fashion.

I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.

My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.

Hopefully this is not too confusing.

Thanks.

BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.