Wednesday, March 21, 2012

Many data files, SAME Filegroup, SAME drive

Hello. I'm looking for any pros/cons to having multiple
files, SAME filegroup, SAME drive... Say you have a
database, where 4 data files reside on the one PRIMARY
Filegroup.
my_db_Data_1.MDF
my_db_Data_2.NDF
my_db_Data_3.NDF
my_db_Data_4.NDF
When you do an action like BULK INSERT a large amount of
data to a table, SQL Server will scatter that table's data
across the 4 data files.
I understand the reasons one might WANT to have 4 files on
teh same drive, like if they want to copy smaller data
files around, etc... But, performance-wise, I'd think it
would be at LEAST somewhat slower writing to 4 files, then
writing to ONE file on the same drive. Any ideas on that?
Also, wouldn't you have to deal with 4 shrinkings instead
of just one?
THanks, BruceBruce
What is a perfomance gain do you get by putting all files on the same
physical disk and the same filegroup?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:01b801c34bc6$55421db0$a401280a@.phx.gbl...
> Hello. I'm looking for any pros/cons to having multiple
> files, SAME filegroup, SAME drive... Say you have a
> database, where 4 data files reside on the one PRIMARY
> Filegroup.
> my_db_Data_1.MDF
> my_db_Data_2.NDF
> my_db_Data_3.NDF
> my_db_Data_4.NDF
> When you do an action like BULK INSERT a large amount of
> data to a table, SQL Server will scatter that table's data
> across the 4 data files.
> I understand the reasons one might WANT to have 4 files on
> teh same drive, like if they want to copy smaller data
> files around, etc... But, performance-wise, I'd think it
> would be at LEAST somewhat slower writing to 4 files, then
> writing to ONE file on the same drive. Any ideas on that?
> Also, wouldn't you have to deal with 4 shrinkings instead
> of just one?
> THanks, Brucesql

No comments:

Post a Comment