Post reply

multiple files what determines where an object goes

  • November 5, 2015 at 1:42 pm

    #306298

    I have a 3rd party database 1.7 terabytes. It has multiple file groups, and in 2 cases 9 files per group. When a object (ie lets say an index is addded with a filegroup specified) what algorithm does sql use to determine where an obect would go?

  • November 5, 2015 at 2:30 pm

    #1838107

    If an object is added with a filegroup specified, then it goes into the files in that filegroup. Probably all of the files in that filegroup. Google "proportional fill algorithm" for more detail.

    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

  • November 6, 2015 at 7:37 am

    #1838230

    tcronin 95651 (11/5/2015)

    I have a 3rd party database 1.7 terabytes. It has multiple file groups, and in 2 cases 9 files per group. When a object (ie lets say an index is addded with a filegroup specified) what algorithm does sql use to determine where an obect would go?

    As Gail said, it uses a proportional fill algorithm, this is why it's important to ensure all files are the same size and growth rate, otherwise I\O can be directed at one file more than others

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

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

  • November 6, 2015 at 8:03 am

    #1838241

    thanks I figured as much, algorithm makes sense

Viewing 4 posts - 1 through 3 (of 3 total)

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