Showing posts with label reside. Show all posts
Showing posts with label reside. Show all posts

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