Post reply

Files and Filegroups

  • May 15, 2009 at 8:11 am

    #200546

    I have a few questions about files and filegroups if someone could explain them further to me.

    1.) I understand (maybe incorrectly) that in a filegroup with multiple files, any table created or moved to that filegroup will be distributed across all files in that filegroup using a "round robin" approach. Is that correct?

    2.) Assuming that #1 is correct, is there any difference, when SQL Server distributes data across the files, between ndf's and mdf's during distribution? Meaning, is there any reason why SQL would NOT use ndf's when evenly distributing data?

    3.) Is there any way I can see what tables are in each file in a given filegroup?

    Thanks for your help.

  • May 15, 2009 at 9:18 am

    #995431

    Paul S. Huebner (5/15/2009)

    I have a few questions about files and filegroups if someone could explain them further to me.

    1.) I understand (maybe incorrectly) that in a filegroup with multiple files, any table created or moved to that filegroup will be distributed across all files in that filegroup using a "round robin" approach. Is that correct?

    2.) Assuming that #1 is correct, is there any difference, when SQL Server distributes data across the files, between ndf's and mdf's during distribution? Meaning, is there any reason why SQL would NOT use ndf's when evenly distributing data?

    3.) Is there any way I can see what tables are in each file in a given filegroup?

    Thanks for your help.

    1- True

    2- SQL evenly distributes the data across all the files using the free space on them to determine where to write.

    3- The tables might be distributed across all the files

  • May 15, 2009 at 9:33 am

    #995449

    Thanks Felix...

    So, with regards to #2...would I guarantee a more evenly distributed table if I sized the newly added files to be bigger than the default 5mb or whatever it is?

    Also, with regards to Index Rebuild maint plans, when it drops and recreates the clusterd indexes, does that mean it will rebuild the clustered index across all the files in the filegroup or does the location of those indexes not change?

    Let me know if that's clear as mud...

    Thanks!

  • May 15, 2009 at 9:38 am

    #995456

    Paul S. Huebner (5/15/2009)

    1.) I understand (maybe incorrectly) that in a filegroup with multiple files, any table created or moved to that filegroup will be distributed across all files in that filegroup using a "round robin" approach. Is that correct?

    2.) Assuming that #1 is correct, is there any difference, when SQL Server distributes data across the files, between ndf's and mdf's during distribution? Meaning, is there any reason why SQL would NOT use ndf's when evenly distributing data?

    There is no difference based on the file's type extension, etc. It's true that SQL Server will use "round robin" to evenly distribute its allocations to the files in a filegroup, but this is only if they all have the same amount of free space. It will initially determine which file has the most free space and allocate to it first. Only if there are multiple files with the most free space (or all have the same amount) will it round-robin between them.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 15, 2009 at 9:50 am

    #995466

    Ah, thanks! Very helpful.

  • May 15, 2009 at 9:58 am

    #995476

    Glad we could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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