locked
DB size is over 1TB and optimise performace RRS feed

  • Question

  • Hi We have production database and few table are grown up to 800GB, 600GB etc. At this moment  Database has 4 mdf files each has  size limit . All datafiles located on U:drive size 2TB.  Some of the tables  size over 300GB . no file group has been created or enabled.  I understand we need space DB  accommodate data  .That is fine. But My question around as follows.

    1. How to optimise performace over data distribution with multiple data files .(Do we need to plan DB filegroups? IS that mean create multiple drives V: drive, X:drive, P: drive etc and distribute datafiles across the drives?

    2.To achieve this create fresh DB and backup and restore  ?

    3. Does it helpful much if the disk level storage allocated from same volume?

    4.new data files design based on average records size on the tables ?

    Any idea wuild be greatly appreciate.

    regards

     

    Monday, April 15, 2019 11:50 PM

Answers

All replies

  • Hi ashwan,

     

    >> How to optimise performace over data distribution with multiple data files .(Do we need to plan DB filegroups? IS that mean create multiple drives V: drive, X:drive, P: drive etc and distribute datafiles across the drives?

    Yes, you can create secondary data files directly and save them on different disks. To maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.

     

     

     

     You can create a second filegroup for the secondary file and make that filegroup the default filegroup. In this way, the primary file will contain only system tables and objects.

     

    Note: Please use tsql to modify the default file group

     

    ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT; GO

     

     

    >>To achieve this create fresh DB and backup and restore  ?

     

    No, this is not necessary.

     

    >>Does it helpful much if the disk level storage allocated from same volume?

     

    I recommend that you store the data files on different physical disks and try to keep the log files and data files in different locations. This will improve performance because parallel disk I/O search will increase data search speed.

     

    >>new data files design based on average records size on the tables ?

     

    The new data file is based on the initialization size and growth mode you configured. You can find the filling strategy for files and file groups through the links below: File and Filegroup Fill Strategy

     

    For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    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

    Tuesday, April 16, 2019 4:28 AM
  • >>>How to optimise performace over data distribution with multiple data files

    So you do not have problem with query performance , users do not complain, right?

    Do you have SSD disks? RealSSD gets up to 50.000 - on one disc, with "weak times" of around 36.000 IOPS. That means that ONE SDD is about 7,5 times as fast - in slow moments - as your 12 disc setup. Around 10 times as fast in good times

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966412(v=technet.10)


    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

    Tuesday, April 16, 2019 4:46 AM
  • Unless you add physical disks, then you won't see any performance difference. Spreading over different filegroups won't help either, unless you can for instance place one rarely accessed low priority table on a slow disk, or vice versa for a high priority table on a fast disk.

    Backup restore will give you a binary copy of the database including file and filegroup layout, so that is not the way to go, if you decide to go this route in the first place (which I doubt will help you).

    Using a vastly superior disk technology might be the answer (SSD), as Uri suggested. Or, else, look at what parts are slow and tune them (traditional performance tuning).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, April 16, 2019 6:48 AM
  • Uri, Thank you for the reply

    I getting Storage from the LUN (created from pool of  disks)  Therefore I don't think we have SSD. In this case create other drive(from on same LUN) or and increase U: drive 2-4-6 TB would be good?

    What is your suggestions .

    regards

    Tuesday, April 16, 2019 8:09 AM
  • Yep to open other drive will god but more ideally from another LUN if it is possible

    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

    Tuesday, April 16, 2019 8:49 AM
  • Hi Uni do you mean different file group on different drives?say(V: drive, X:drive, P: drive etc ) or different filegroups on same drive(say U: drive)

    Is that mean are we trying to achieve performance by parallel IOs on disks?

    Let say we add new drive(X:) . How the new drive will recognised by SQL Server as it has not defined while installation.

    regards

    Tuesday, April 16, 2019 7:53 PM
  • >>>Hi Uni do you mean different file group on different drives?say(V: drive, X:drive, >>>P: drive etc ) or different filegroups on same drive(say U: drive)

    I meant different file group on different drives

    >>.Let say we add new drive(X:) . How the new drive will recognised by SQL Server >>>as it has not defined while installation.

    You add a new drive and the server will recognize it... Nowadyas we can add a new drive while system is online....


    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, April 17, 2019 5:06 AM
  • Hi ashwan,

     

    As mentioned by Uri,  the current server supports hot plugging. As long as you add a new disk, the system will recognize it. You only need to add a new data file to the new disk and add it to the new filegroup to implement parallel I/O.

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    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

    Wednesday, April 17, 2019 5:58 AM