Post reply

Solution to do maintenance the large table

  • November 20, 2014 at 5:43 am

    #313271

    Here is the situation, I have oltp database about 6 tb and there are couple tables they are very huge one is 1.5 tb another is 700 gb.. We have minimal downtime. Tables are not partitioned. This database is setup for availability group, rebuilding index is taking forever.. What could be the best solution for index maintenance. We have had big performance issue while doing rebuild.. Re-org takes forever.. If re-org completes does this fragment to real down? Any idea??? Any solution?? Any temporary solution? Any long term solution?

  • November 22, 2014 at 6:48 am

    #1760325

    Any idea ?

  • November 24, 2014 at 3:03 pm

    #1760625

    How fragmented are the indexes on the tables?

    Were you doing online index rebuilds?

  • November 24, 2014 at 8:51 pm

    #1760681

    I was doing online but issue was on rollback when it had to killed.. Took for ever.. Any other sophisticate way to do index maintenence without having rollback? Does re-org do the same level as re build does ?

  • November 25, 2014 at 10:06 am

    #1760870

    Per Kendra Little on Brent Ozar's site:" you can reorganize for a while and then stop without facing a massive rollback"

  • November 25, 2014 at 10:09 am

    #1760872

    Still you have not indicated the level of fragmentation of the tables.

    What is the % fragmentation?

    Do you have a test system on which you can test rebuilding and see how long it will take?

  • November 25, 2014 at 1:44 pm

    #1760904

    it was 28% fragmented

  • November 25, 2014 at 1:47 pm

    #1760907

    last time as i remember we were able to rebuild successfully to that large table with online on tooks about 20 hr...

  • November 25, 2014 at 2:24 pm

    #1760920

    Books online suggest reorg if fragmentation is < 28%

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

  • November 25, 2014 at 3:22 pm

    #1760938

    Yea, but if we don't have option to do re-build.. what end up happen if this large table gets more then 40% fragment, trying to see the better option if with out having roll back.... i am in the process of doing re-org.. but improvement is just 40% drop to 38? what is the ration of improvement by re-org in compare to rebuild, do you have any experience or anybody who has experience with?

  • November 25, 2014 at 5:54 pm

    #1760947

    I would say monitor the fragmentation and keep doing reorg.

    Then look at the rebuild options, in particular parallelism.

    Also when you run rebuild, make sure your data files and log files are pre-grown to handle the work.

    It is possible that the performance hit you got when running the rebuild was due to stalls related to auto-grow of both the data file and log file.

    You probably need free space in both the log file and data file about 1.5 times the size of the bigger of the two tables.

    Rebuild indexes on one table. Dump the log and make sure it is empty. Then rebuild indexes of the second table

  • November 25, 2014 at 7:16 pm

    #1760948

    arnipetursson (11/25/2014)

    Books online suggest reorg if fragmentation is < 28%

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

    On a large table, 28% is an awful lot of fragmentation. Further, reorganization is fully logged no matter what the Recovery Model is and, what really sux about it, it that it only defrags the leaf level. It doesn't defrag the B-TREE.

    Shifting gears a bit and just in case someone brings it up, some will say that's not important and, recently, some pretty big names in the business have said that you don't need to defrag at all. That "can" be true if you're not ever doing much more than reading one row at a time using a GUI or if you only have indexes that have a leading column that represents the normal order of insertion but if you want any reasonable performance for reports or overnight batch runs, you better plan on keeping a pretty tight ship both for fragmentation levels and statistics.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 25, 2014 at 7:20 pm

    #1760949

    Sagar-636902 (11/20/2014)

    Here is the situation, I have oltp database about 6 tb and there are couple tables they are very huge one is 1.5 tb another is 700 gb.. We have minimal downtime. Tables are not partitioned. This database is setup for availability group, rebuilding index is taking forever.. What could be the best solution for index maintenance. We have had big performance issue while doing rebuild.. Re-org takes forever.. If re-org completes does this fragment to real down? Any idea??? Any solution?? Any temporary solution? Any long term solution?

    You've identified the fix for this problem in your explanation above. which edition of SQL Server 2012 are you using?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 25, 2014 at 7:37 pm

    #1760952

    Thanks both for the Reply,it is sql server 2012 enterprise edition sp1

  • November 25, 2014 at 7:54 pm

    #1760953

    i did not get it 'Then look at the rebuild options, in particular parallelism.' we don't have partitions.

Viewing 15 posts - 1 through 15 (of 19 total)

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