locked
Want know why we are creating more data files RRS feed

  • Question

  • Hi All,

    I our environment each and every database they created more than 5 data file in same drive. Its is best practice and l want know it may create any performance issue (database size 200 GB).

    For more than 800 GB they created 15 data file in 2 different drive. Its a good practice Please some one suggest me and every data file growth is enable.

    Thursday, January 21, 2016 6:21 PM

Answers

  • Unless all the data files are on separate physical drives you are not going to get any performance mileage. If you create 5 data files all residing on single drive they would all be competing for same I/O so what benefit would you get.

    Only benefit of many data files I know is when you want to perform restoration of big database. if you have multiple data files you can restore with multiple files on different drive. Consider a single 1 TB database with 1 mdf itrs quite hard to find 1TB drive and that too all dedicated to SQl Server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Friday, January 22, 2016 7:53 AM
    Answerer
  • That sounds like one of the "best practices" that have been floating around based mostly on partial facts and lots misinterpretations and urban legends - basically it doesn't work that way. 

    There are good reasons for having multiple files/filegroups to improve performance, manageability and availability. However, simply creating those files on the same drive gets you pretty much nothing and in some edge cases, you might even suffer performance degradation when you have lots of files in the same filegroup.

    When you have multiple files without explicit physical design to place certain objects in specific files/filegroups, the files just get filled in a round robin fashion. SQL Server attempts to keep usage of each file fairly proportional (they're all filled somewhat equally). Since you didn't mention creating additional filegroups, all you're doing is spreading the data across multiple files and they're all hitting the same drive. You might get some manageability gains with smaller file sizes but it's mostly just on paper. We have TB sized SQL Server data files running happily for years.

    As for file size growth management, there are lots of articles from Microsoft, MVPs, partners and experienced users out there. Most of them pretty much say the same thing. Pick reasonable numbers based on your workload. Here are a few good reads to get started:

    http://sqlmag.com/storage/sql-server-storage-best-practices

    http://www.brentozar.com/sql/sql-server-san-best-practices/

    http://www.sqlskills.com/blogs/paul/importance-of-data-file-size-management/


    No great genius has ever existed without some touch of madness. - Aristotle

    Thursday, January 21, 2016 8:50 PM

All replies

  • It depends very much also on the application or database consumes, enviroment, etc... but particularly, I would do this:

    Separate the System Databases (mainly Tempdb) of this drive.

    Separate the log transaction file of this drive, rather with RAID 1+0, to improve data write performance.


    • Edited by MarcosLanzarini Thursday, January 21, 2016 6:54 PM Add: enviroment, etc
    Thursday, January 21, 2016 6:42 PM
  • Adding more files will not create a performance problem with the database, it may help a performance problem.  However, there are many variables as to whether it will help or not.

    Thursday, January 21, 2016 7:12 PM
    Answerer
  • That sounds like one of the "best practices" that have been floating around based mostly on partial facts and lots misinterpretations and urban legends - basically it doesn't work that way. 

    There are good reasons for having multiple files/filegroups to improve performance, manageability and availability. However, simply creating those files on the same drive gets you pretty much nothing and in some edge cases, you might even suffer performance degradation when you have lots of files in the same filegroup.

    When you have multiple files without explicit physical design to place certain objects in specific files/filegroups, the files just get filled in a round robin fashion. SQL Server attempts to keep usage of each file fairly proportional (they're all filled somewhat equally). Since you didn't mention creating additional filegroups, all you're doing is spreading the data across multiple files and they're all hitting the same drive. You might get some manageability gains with smaller file sizes but it's mostly just on paper. We have TB sized SQL Server data files running happily for years.

    As for file size growth management, there are lots of articles from Microsoft, MVPs, partners and experienced users out there. Most of them pretty much say the same thing. Pick reasonable numbers based on your workload. Here are a few good reads to get started:

    http://sqlmag.com/storage/sql-server-storage-best-practices

    http://www.brentozar.com/sql/sql-server-san-best-practices/

    http://www.sqlskills.com/blogs/paul/importance-of-data-file-size-management/


    No great genius has ever existed without some touch of madness. - Aristotle

    Thursday, January 21, 2016 8:50 PM
  • Unless all the data files are on separate physical drives you are not going to get any performance mileage. If you create 5 data files all residing on single drive they would all be competing for same I/O so what benefit would you get.

    Only benefit of many data files I know is when you want to perform restoration of big database. if you have multiple data files you can restore with multiple files on different drive. Consider a single 1 TB database with 1 mdf itrs quite hard to find 1TB drive and that too all dedicated to SQl Server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Friday, January 22, 2016 7:53 AM
    Answerer
  • Thank you very much to all

    Friday, January 22, 2016 1:05 PM
  • Good Day,

    More data files will help to increase the performance especially if you have 800 GB Database. Check the design of the database , Table properties and check for any table partition. The DBA may be using table partitioning to manage the data. Or the big tables are moved to the separate data files. There is a specific reason for this design and it will be mostly to get high performance. Good Luck.

    Thanks 


    http://sqllive.wordpress.com/

    Monday, January 25, 2016 5:08 AM
  • Performance Thinking:-

    Identify the large tables

    Identify Complex processes

    Identify heavily accessed table

    Identify Less accessed tables

    Put different tables used in the same join queries in different file groups of differ physical drive.

    See and read more on  I/O request flow : -

             


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Thursday, January 28, 2016 7:03 AM