Post reply

What is better: single or multiple datafile (mdf) in user database for better performance?

  • December 21, 2012 at 12:20 am

    #286784

    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

    #1570755

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 21, 2012 at 1:13 am

    #1570766

    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

    #1570767

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 21, 2012 at 1:21 am

    #1570768

    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

    #1570769

    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