advantages of having multiples files in a filegroup

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    Guys,

    I have a requirement to create a database. I've two file groups DATA (default) & INDEX on separate disks. Is there any advantage of creating multiple files for the DATA file group(in the same disk or separate disks). Please share your thoughts.

    Many thanks.

  • benjamin.reyes

    SSCertifiable

    Points: 5249

    What does your disk architecture look like?

    What do the query load patterns look like for your server?

    Would piecemeal recovery be beneficial to you?

    Do you have your emergency restore scripts in order to deal with the different files?

    ...sometimes you just need to do what the vendor says to maintain their support.

  • johnwalker10

    SSCrazy Eights

    Points: 9074

    Having multiple file in a filegroup helps you to increase sql server performance in some secnarios. Take a look at this article may this this will help you: http://blog.idera.com/sql-server/performance-and-monitoring/increase-sql-server-performance-using-multiple-files/

  • Gail Shaw

    SSC Guru

    Points: 1004484

    On the same disk, none at all. On different disks, maybe. Are you seeing IO contention? If so, is the 'different disk' really different hardware? If yes to both, then you may get performance improvements.

    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
  • Hugo Kornelis

    SSC Guru

    Points: 64685

    GilaMonster (1/6/2016)


    On the same disk, none at all.

    ... in most scenarios.

    If you have a database that has a very high amount of table allocations and deallocations, you can get some contention on the GAM, SGAM or PFS pages. By using multiple files, you spread out the load and reduce that contention.

    In practice, this usage pattern rarely happens in user database. It is however quite common in tempdb. That is the reason why most best practises guidelines tell you that you need to create multiple data files for tempdb (allthough opinions differ on what the best rule of thumb for the number of files is).

    (Also note that using multiple files for tempdb is best combined with (a) making sure that all those files are the same size and have the same autogrow setting, and (b) enabling trace flag 1118 to ensure that they remain equal-sized. You might also want to enable trace flag 1117 to further optimze tempdb usage. Google for the details).

    On different disks, maybe. Are you seeing IO contention? If so, is the 'different disk' really different hardware? If yes to both, then you may get performance improvements.

    Absolutely agree here. If the database is constantly hitting the same file hard, then you can gain poerformance by ensuring that the same data is spread out over multiple physical devices.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    Thanks everyone!

  • Phil Parkin

    SSC Guru

    Points: 244732

    Hugo Kornelis (1/6/2016)


    GilaMonster (1/6/2016)


    On the same disk, none at all.

    ... in most scenarios.

    If you have a database that has a very high amount of table allocations and deallocations, you can get some contention on the GAM, SGAM or PFS pages. By using multiple files, you spread out the load and reduce that contention.

    In practice, this usage pattern rarely happens in user database. It is however quite common in tempdb. That is the reason why most best practises guidelines tell you that you need to create multiple data files for tempdb (allthough opinions differ on what the best rule of thumb for the number of files is).

    (Also note that using multiple files for tempdb is best combined with (a) making sure that all those files are the same size and have the same autogrow setting, and (b) enabling trace flag 1118 to ensure that they remain equal-sized. You might also want to enable trace flag 1117 to further optimze tempdb usage. Google for the details).

    On different disks, maybe. Are you seeing IO contention? If so, is the 'different disk' really different hardware? If yes to both, then you may get performance improvements.

    Absolutely agree here. If the database is constantly hitting the same file hard, then you can gain poerformance by ensuring that the same data is spread out over multiple physical devices.

    Interesting, thanks Hugo.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Hugo Kornelis (1/6/2016)


    GilaMonster (1/6/2016)


    On the same disk, none at all.

    ... in most scenarios.

    If you have a database that has a very high amount of table allocations and deallocations, you can get some contention on the GAM, SGAM or PFS pages. By using multiple files, you spread out the load and reduce that contention.

    Don't think I've ever seen a user database manage to do that. I hate to think of the kind of app design/db design that would manage it. :sick:

    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
  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    Can I create just one file for now for a specific file group and create additional files if required in the future? Is this a good approach?

  • Gail Shaw

    SSC Guru

    Points: 1004484

    You can, but proportional fill will mean that the new file will get the bulk of new data. Trying to balance that out won't be trivial (rebuild all clustered indexes)

    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
  • benjamin.reyes

    SSCertifiable

    Points: 5249

    Here are some published results.

    http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/[/url]

    http://www.sqlskills.com/blogs/paul/benchmarking-multiple-data-files-on-ssds-plus-the-latest-fusion-io-driver/[/url]

    But, don't create a problem trying to solve one you may not have. Do you own benchmarks in your own environment and make changes based on what's relevant to you.

Viewing 11 posts - 1 through 11 (of 11 total)

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