Design Questions on File Groups and Files

In general, what are the advantages and disadvantages to have more data file
s
vs. less data files? Thanks.

Kathy,

Assuming filegroups are used as well:

1.  Advanced placement of database objects
2.  Seperation of table and indexes incl., seperation of system objects from
user-defined objects
3.  Possible increase in performance w/ RAID or w/o RAID -- results will
vary
4.  Expansion of the database onto seperate physical drives
5.  Faster backups for larger databases

That being said most smaller to medium sized databases probably do not need
to use additional files and filegroups (just an opinion).

HTH

Jerry

"Kathy" <Kathy@discussions.microsoft.com> wrote in message
news:6AEA9FF8-63C8-4A2A-8824- C3787CB79B04@microso
ft.com...
> In general, what are the advantages and disadvantages to have more data
> files
> vs. less data files? Thanks.

Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
files? and/or how many is considered too many?

"Jerry Spivey" wrote:

> Kathy,
>
> Assuming filegroups are used as well:
>
> 1.  Advanced placement of database objects
> 2.  Seperation of table and indexes incl., seperation of system objects fr
om
> user-defined objects
> 3.  Possible increase in performance w/ RAID or w/o RAID -- results will
> vary
> 4.  Expansion of the database onto seperate physical drives
> 5.  Faster backups for larger databases
>
> That being said most smaller to medium sized databases probably do not nee
d
> to use additional files and filegroups (just an opinion).
>
> HTH
>
> Jerry
>
> "Kathy" <Kathy@discussions.microsoft.com> wrote in message
> news:6AEA9FF8-63C8-4A2A-8824- C3787CB79B04@microso
ft.com... 
>
>
>

I'm not aware of any issues nor do I have a "too many" count.  I would base
it off of need...i.e., if you need an advantage exposed by using
file/filegroups the use them...if not, then I wouldn't.  Additional
files/filegroups can make it a little more challenging to administer (i.e.,
future movement of the objects - emptying files etc...)

HTH

Jerry
"Kathy" <Kathy@discussions.microsoft.com> wrote in message
news:34B97E17-7CF2-4825-8173- 3F03E35B8367@microso
ft.com...
> Thanks Jerry. Then what are disadvantages (oeverhead) to have more data
> files? and/or how many is considered too many?
>
> "Jerry Spivey" wrote:
> 

There is a debating here whether or not the number of files has significant
performance impact. More specifically, one data file per filegroup vs four
files per filegroup, for example. Do you have experience on it? Thanks.

"Jerry Spivey" wrote:

> I'm not aware of any issues nor do I have a "too many" count.  I would bas
e
> it off of need...i.e., if you need an advantage exposed by using
> file/filegroups the use them...if not, then I wouldn't.  Additional
> files/filegroups can make it a little more challenging to administer (i.e.
,
> future movement of the objects - emptying files etc...)
>
> HTH
>
> Jerry
> "Kathy" <Kathy@discussions.microsoft.com> wrote in message
> news:34B97E17-7CF2-4825-8173- 3F03E35B8367@microso
ft.com... 
>
>
>

Kathy,

Check out:

http://www.databasejournal.com/feat...cle.php/1439801
and
http://www.sql-server-performance.com/filegroups.asp

HTH

Jerry
"Kathy" <Kathy@discussions.microsoft.com> wrote in message
news:8A0149F3-1A3E-499D-B844- 0E8C2F647317@microso
ft.com...
> There is a debating here whether or not the number of files has
> significant
> performance impact. More specifically, one data file per filegroup vs four
> files per filegroup, for example. Do you have experience on it? Thanks.
>
> "Jerry Spivey" wrote:
> 

Jerry. Thanks very much. The included articles are very helpful to understan
d
the issue.

"Jerry Spivey" wrote:

> Kathy,
>
> Check out:
>
> http://www.databasejournal.com/feat...cle.php/1439801
> and
> http://www.sql-server-performance.com/filegroups.asp
>
> HTH
>
> Jerry
> "Kathy" <Kathy@discussions.microsoft.com> wrote in message
> news:8A0149F3-1A3E-499D-B844- 0E8C2F647317@microso
ft.com... 
>
>
>