Post reply

files ndf

  • August 23, 2013 at 2:04 am

    #276403

    Hello.

    I need several files ndf because the size DB is more or less 600 GB.

    For example, with the historic data, what is the best, several files with fewer GB or one or two big files ?

    500 users work with DB.

    Thanks.

  • August 23, 2013 at 2:33 am

    #1644283

    What's your disk layout? If you can spread the files over multiple LUN's, then you should see an improvement in I/O performance. If you can use multiple filegroups, then you'll get the benefit of piecemeal restores. It'll be easier for you if you split it up based on logical divisors, e.g. annually, monthly, etc.

  • August 23, 2013 at 2:51 am

    #1644294

    Most of the time a database benefits from more then one datafile. Also use multiple filegroups to seperate your data (historical vs. actual and/or clutered indexes vs. non-clustered indexes and/or ....). Try to keep the same filesize (and autogrowth) of all datafiles within a filegroup.

    With very large databases (VLDB) you need to find a balance between the number of datafiles and the size of the datafiles. If one or the other becomes too large it will be harder to administer/manage. For example: if you need to move a datafile with a size of 400GB it could take so much time you could not perform this in the timeframe window you have available according to your SLA.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • August 23, 2013 at 3:14 am

    #1644302

    My idea is several filegroups with partition scheme, because i need to separate data in 4 geoghraphics areas and also to separate historic, configuration and master data.

    For that, my question was if one big file (64 GB) per filegroup or, for example, 4 files 16GB.

    Thanks for all.

  • August 23, 2013 at 3:20 am

    #1644303

    Specific for the more write intensive filegroups the performance will benefit with 4 datafiles vs. 1 datafile. With 4 datafiles the I/O will be spread over all datafiles (when they are the same size).

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • August 23, 2013 at 3:35 pm

    #1644588

    HanShi (8/23/2013)

    Specific for the more write intensive filegroups the performance will benefit with 4 datafiles vs. 1 datafile. With 4 datafiles the I/O will be spread over all datafiles (when they are the same size).

    But there would only be a point with it, if the files are on different disks, wouldn't it?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply