| Author |
Design Questions on File Groups and Files
|
|
|
| In general, what are the advantages and disadvantages to have more data files
vs. less data files? Thanks.
| |
| Jerry Spivey 2005-09-29, 1:23 pm |
| 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 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...
>
>
>
| |
| Jerry Spivey 2005-09-30, 3:23 am |
| 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...[color=darkred]
> 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 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...
>
>
>
| |
| Jerry Spivey 2005-09-30, 1:23 pm |
| 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...[color=darkred]
> 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:
>
| |
|
|
|
|