December 21, 2012 at 12:20 am
Hi,
I have a user database with 30GB mdf file. The datafile are in a SAN (RAID 5 with four disk).
The mdf file grows more or less 5GB in a year.
What is better to have a unique datafile or have multiple datafile for better performance (note: i only have one phisical disk
for datafile)? In case that is better to have multiple datafiles, what would be the best configuration for them
(fixed size, all of them with the sime size or not, and how many files)? there are any guidelines for it?
Many thanks in advance.
December 21, 2012 at 12:43 am
ico-601891 (12/21/2012)
What is better to have a unique datafile or have multiple datafile for better performance
It depends. There is no simple answer to that question. Depends on the physical disk layout, the bottlenecks that the database is encountering, the way the data is used and a whole bunch of other considerations
(note: i only have one phisical disk for datafile)?
In that case, one file. There's no advantage for performance for having multiple files on the same drive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 1:13 am
Thanks,
But would be some performance penalty if I define my database with multiple files in the same disk? I ask it because i have tow aditional user database with some fixed size datafiles on the same disk. One only have PRIMARY filegroup and in the other i have multiple FILEGROUP having no user objects in PRIMARY filegroup. In this cases, i have to redefine the databases to have only one datafile or the performance would be the same as if it had only one file? in case i have to redefine the database, what would be the best (and fastest because the mentioned database are production databases) way to do it.
Many thanks for all your help.
December 21, 2012 at 1:18 am
ico-601891 (12/21/2012)
But would be some performance penalty if I define my database with multiple files in the same disk?
Not unless you have hundreds of files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 1:21 am
What has likely been done is to setup the additional data files for recover-ability and not performance. The separation you have described is done often times when doing filegroup backups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2012 at 1:22 am
ok.
Many thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply