Post reply

Adding files to a filegroup

  • November 21, 2008 at 5:14 am

    #201651

    Hello,

    This post relates to our 300GB database which was originally designed with only one filegroup and one file.

    I have recently added 7 more files to the primary filegroup (8 cpu machine) for a performance gain strategy. The first and original file contains 300GB and the other 7 files are empty 25GB files. What I would like to accomplish is the 300GB to be striped evenly over the 7 files.

    Is this possible? Right now the new files are just empty and the primary first file still contains all the data.

    Thanks and kind regards,

    Glen

  • November 21, 2008 at 5:41 am

    #901553

    well... that's the expected behavior.

    Are you expecting SQL Server to load balance your objects accross the filesystems?

    It would happen on Oracle+ASM environment but as far as I know SQL Server doesn't do that for you.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • November 21, 2008 at 7:37 am

    #901616

    Just creating more files won't help. SQL will only use the new files as they are needed. They need to be spread over more drives to get any benefit.

    If you create a new filegroup, then you can move data into the filegroup on another drive. For instance, all your data might be in the PRIMARY filegroup in F:\MSSQL\DATA\My_Database_Data1.mdf

    You can create a new filegroup called INDEXES. Then create a new file: G:\MSSQL\DATA\My_Database_Indexes.ndf Then recreate the indexes into the new file. This will spread the load out over F & G.

  • November 21, 2008 at 7:48 am

    #901625

    Thanks for the replies. My second strategy is the seperation of index and data in two filegroups.

    I've read in several forums and books that there is an I/O performance gain when you align the number of files to the number of cpu's in your machine to have parrallel threading. It's even mentioned in the Microsoft Training Kit books.

    Any thoughts on that?

  • November 21, 2008 at 7:52 am

    #901627

    Glen Loupias (11/21/2008)

    Thanks for the replies. My second strategy is the seperation of index and data in two filegroups.

    I've read in several forums and books that there is an I/O performance gain when you align the number of files to the number of cpu's in your machine to have parrallel threading. It's even mentioned in the Microsoft Training Kit books.

    Any thoughts on that?

    I haven't heard about that .... but then there's so much that I don't know !! I've heard that having multiple tempdb files can help even if they're on the same drive.

  • November 21, 2008 at 7:55 am

    #901628

    I already made 8 tempdb files for I/O performance gain 🙂

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

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