Post reply

Data Files

  • September 30, 2011 at 5:26 am

    #258152

    I was wondering if somebody could clear something up for me:

    I have inherited a system which has a SQL database which has 2 PRIMARY Data Files. ... Data_1.mdf and Data_2.mdf. As far as I can see there are no file groups defined and nothing in the database defined for what should go to which filegroup.

    Does this provide any benefits or optimisations to the database?

    What is actually going into each file? Does the database determine this itself? How?

    Thanks in advance for any help!

  • September 30, 2011 at 5:33 am

    #1388793

    When you create an object (say a table), it goes to PRIMARY file group (unless you specify any different). SQL Server decides how to manage data of this table in PRIMARY file group (& files) based on various conditions.

  • September 30, 2011 at 5:37 am

    #1388794

    Hi ~Dev~

    Great, thanks! So SQL will determine what goes where by itself. Is there any documentation on how it determines this? Just so I can learn a bit more about it.

    Also, is there any benefit from this, or is one file a better option? Considering they're both stored on the same file system.

  • September 30, 2011 at 5:41 am

    #1388795

    Please go through following article. It will answer all your questions.

    Files and Filegroups Architecture:

    http://msdn.microsoft.com/en-us/library/ms179316.aspx

  • September 30, 2011 at 5:44 am

    #1388797

    Performance gain if data files are on different spindles. Sql uses the files proportionally based on free space in each file. Objects are spread across files in a file group and you have no control at the file level, only at the file group.

  • September 30, 2011 at 5:46 am

    #1388801

    Sorry.wrong post

    M&M

  • September 30, 2011 at 5:49 am

    #1388803

    In your case, Primary Filegroup is the default. Regarding filling, a proportional fill theory concept is getting used in SQL Server.

    You could read more about it from below link which has details of File and Filegroup fill strategy.

    http://msdn.microsoft.com/en-us/library/ms187087.aspx

    M&M

  • September 30, 2011 at 5:49 am

    #1388804

    This article has about the best explanation of how the data is split across multiple data files in the same File Group:

    Using Files and Filegroups

    Effectively data is split ("striped") fairly evenly at the extent level for data files of equal sizes.

Viewing 8 posts - 1 through 7 (of 7 total)

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