SQL Server Performance

File/Filegroups

Discussion in 'SQL Server 2008 General DBA Questions' started by RVG, Sep 23, 2010.

  1. RVG Member

    Hi,
    I have a two files in a particular file group then when I insert 1000 records worth of data into the table A that is created on a file-group that has two files in as I mentioned earlier. But I'm wondering how will be the data spread across the files in those file groups will the data be spread 500 and 500 respectively into two file groups or else how. can any one explain me this scenario please. As far I know SQL Server Will automatically chose how much data it should push into those files.
    Thanks,
    Ravi
  2. FrankKalis Moderator

    Check out the topic "file fill strategy" in BOL. To quote:
    "Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
    As soon as all the files in a filegroup are full, the Database Engine automatically expands one file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically. For example, a filegroup is made up of three files, all set to automatically grow. When space in all the files in the filegroup is exhausted, only the first file is expanded. When the first file is full and no more data can be written to the filegroup, the second file is expanded. When the second file is full and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full and no more data can be written to the filegroup, the first file is expanded again, and so on."
  3. satya Moderator

    It would also depends on the default filegroup that is chosen...

Share This Page