locked
Multiple file groups or multiple files for a large DB RRS feed

  • Question

  • We are migrating a large OLTP database from Sybase to MSSQL Server. Database size is about 3.5TB (and it grows in future) and there is only a single data file (one mdf file on primary, some one has created it). 

    Now, after looking at tables - we see there are 5 large tables contributing to 70% of DB size. And the underlying storage is Pure FlashArray, new prod server has separate drives for Data, Log, TempDB and these are formatted with 4K NTFS allocation size.

    As this is flash storage,

    i. would spreading the data over multiple files (i can bcp the data again from sybase) or

    ii. create multiple filegroups for large tables and create multiple flies in each filegroup

    can help in performance and maintenance of this large DB?

    Let me know for any questions.

    Wednesday, May 8, 2019 5:31 AM

Answers

  • Hi Anil Krishna,

    There is no clear criteria that how many LUNs do you need and what's the ideal size for each LUN. I would prefer to test workloads to see which option is better for your situation. You may want to refer to Microsoft SQL Server Quick Reference and Multipath-IO and Storage Settings.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 9, 2019 6:00 AM
  • >>should i really need to place them on different drives or i can just create multiple filegroups for large >>>tables and place them all in the same drive. 

    You will benefit only if you put the files  on the separated physical drives 

    >>>>SQL DB data files in this drive, should i now go with multiple LUN's for data drives? 

    Yep it would be ideal to have mdf on one lun and log file an another 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, May 12, 2019 5:43 AM
  • as we are using Pure FlashArray faster storage...should i really need to place them on different drives or i can just create multiple filegroups for large tables and place them all in the same drive. There is only one LUN for data drive in my system, with size of 5TB. I am placing all my SQL DB data files in this drive, should i now go with multiple LUN's for data drives? or just multiple filegroups in the same LUN.

    I would expect no performance benefit of multiple filegroups in this case although there could be other benefits like piecemeal restore of large tables. Personally, I wouldn't bother going the effort of multiple filegroups for user data but would create multiple smaller files for manageability.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, May 12, 2019 10:35 AM
    Answerer

All replies

  • https://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/

    One more

    https://dba.stackexchange.com/questions/162114/multiple-filegroups-vs-multiple-files-in-a-single-filegroup


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, May 8, 2019 7:08 AM
  • Thanks Uri Dimant, my concern more here is - as we are using Pure FlashArray faster storage...should i really need to place them on different drives or i can just create multiple filegroups for large tables and place them all in the same drive. There is only one LUN for data drive in my system, with size of 5TB. I am placing all my SQL DB data files in this drive, should i now go with multiple LUN's for data drives? or just multiple filegroups in the same LUN. However, log files and tempdb (data+log) are having dedicated LUN.
    Wednesday, May 8, 2019 2:35 PM
  • Are you going to do the table partitioning on those big tables? If yes, it is better to use the different data files and file groups.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 8, 2019 2:45 PM
  • Thanks Guoxing for your inputs, we are not currently planning it. I'm thinking more about moving the large tables to different filegroups. so, reading and asking questions about it.
    Wednesday, May 8, 2019 3:04 PM
  • Hi Anil Krishna,

    There is no clear criteria that how many LUNs do you need and what's the ideal size for each LUN. I would prefer to test workloads to see which option is better for your situation. You may want to refer to Microsoft SQL Server Quick Reference and Multipath-IO and Storage Settings.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 9, 2019 6:00 AM
  • >>should i really need to place them on different drives or i can just create multiple filegroups for large >>>tables and place them all in the same drive. 

    You will benefit only if you put the files  on the separated physical drives 

    >>>>SQL DB data files in this drive, should i now go with multiple LUN's for data drives? 

    Yep it would be ideal to have mdf on one lun and log file an another 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, May 12, 2019 5:43 AM
  • as we are using Pure FlashArray faster storage...should i really need to place them on different drives or i can just create multiple filegroups for large tables and place them all in the same drive. There is only one LUN for data drive in my system, with size of 5TB. I am placing all my SQL DB data files in this drive, should i now go with multiple LUN's for data drives? or just multiple filegroups in the same LUN.

    I would expect no performance benefit of multiple filegroups in this case although there could be other benefits like piecemeal restore of large tables. Personally, I wouldn't bother going the effort of multiple filegroups for user data but would create multiple smaller files for manageability.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, May 12, 2019 10:35 AM
    Answerer