re-vamping data files in a production database

  • (SQL 2012 Standard)

    I have a database with one 320 .mdf file and one 200GB .ndf file.

    I would like to reconfigure my database to have four 200GB .mdf files. How do I get from here to there?

    Thanks,

    Kevin

  • First things first.

    Why?

    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
  • It is my understanding that is a best-practice. To have multiple data files which are the same size. Performance should be better, right?

  • Do you want these 4 files to share the same filegroup or to have dedicated filegroups?

    For the former just add 3 more files to a primary or another filegroup that you have.

    For the latter :

    1. Create 4 filegroups

    2. Create 4 files mapping 1-1 to filegroups

    3. Re-create clustered (and optionally non-clustered, depending on your strategy) indexes on new FG, use ON ... clause for this.

    Gain in performance can be achieved only if you spread them on separate physical spindles and make research and calculations of I/O usage statistics beforehand.

  • Firstly, are both files in the same filegroup??

    sextonk (12/23/2014)


    It is my understanding that is a best-practice. To have multiple data files which are the same size. Performance should be better, right?

    It's amazing how this myth just keeps getting misconstrued and twisted out of all proportion!!

    No, that's actually a best practice to do with TempDB only, designed to overcome allocation contention.

    Allocating multiple files in the same filegroup will have some overhead due to the proportional fill algorithm which has to manage the file rates and ultimately the file sizes.

    What is it you are trying to achieve, it may be more pertinent to create multiple filegroups and spread objects between said filegroups, but don't just rush into this

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • sextonk (12/23/2014)


    It is my understanding that is a best-practice. To have multiple data files which are the same size.

    It is, for TempDB and only for TempDB. It is not, and never has been, a recommendation for user databases.

    Deciding on file splits for user databases are far more complex and should not be done without first deciding why (performance or partial backup/restore are the usual reasons), testing to ensure that the design meets the requirements and doing sufficient monitoring and analysis to ensure that the changes are necessary in the first place.

    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

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

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