Post reply

Need to rebuild Index on 1.4 TB table (Replication is setup on that database) and also on 900 GB table(mirroring is setup on database

  • April 21, 2016 at 7:47 am

    #313435

    Hello,

    I need to rebuild Index on below Huge tables for our two databases

    1. 1.4 TB table ( Replication is setup on that database)(8 non-clustered indexes, biggest index size is 800GB)

    2. 900GB table (Mirroring is setup on that database)(6 non-clustered indexes, biggest index size is 600GB)

    Environment :- SQL server 2008 Enterprise edition

    we did not rebuild index from last 2 Years on those huge tables and it causes performance issue now.

    So need to rebuild index.

    Anyone has any idea or done this kind of index rebuild before?

    Any kind of suggestion or Idea is appreciated

    Thanks in advance

  • April 21, 2016 at 9:10 am

    #1873674

    kiran.patel07 (4/21/2016)

    Hello,

    I need to rebuild Index on below Huge tables for our two databases

    1. 1.4 TB table ( Replication is setup on that database)(8 non-clustered indexes, biggest index size is 800GB)

    2. 900GB table (Mirroring is setup on that database)(6 non-clustered indexes, biggest index size is 600GB)

    Environment :- SQL server 2008 Enterprise edition

    we did not rebuild index from last 2 Years on those huge tables and it causes performance issue now.

    So need to rebuild index.

    Anyone has any idea or done this kind of index rebuild before?

    Any kind of suggestion or Idea is appreciated

    Thanks in advance

    There is no rocket-science here. If it must be done it must be done. Either you have sufficient hardware to the task or you do not. Since you haven't done this in several years my guess is that you do not or you might have been doing this more frequently. I also note that you have at least 2 year old hardware, which makes me wonder as well.

    Having said that, there are a bunch of BAD default settings in Windows and SQL Server that kill most instances I have come across at clients. You should also pre-size your tlog to keep it from growing during this evolution. Perhaps database size too. And tempdb if it is on different spindles could be sized and used for sorting.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 21, 2016 at 12:59 pm

    #1873766

    Thanks a lot Kevin for you suggestion.

    Hardware perspective, we can't do anything. we have New hardware and SQL 2014 for other products but this one is old.

    Moreover, some other DBA's used to work on this. Big story :-)..

    Anways, come to the point I checked everything about drive size, log size and tempdb. we have enough space to rebuild index.

    My question is, Replication is setup so worried about what happen to replication. And same thing for Mirroring database.

    Do you have any recommendation?

    Thanks

  • April 21, 2016 at 2:07 pm

    #1873778

    kiran.patel07 (4/21/2016)

    Thanks a lot Kevin for you suggestion.

    Hardware perspective, we can't do anything. we have New hardware and SQL 2014 for other products but this one is old.

    Moreover, some other DBA's used to work on this. Big story :-)..

    Anways, come to the point I checked everything about drive size, log size and tempdb. we have enough space to rebuild index.

    My question is, Replication is setup so worried about what happen to replication. And same thing for Mirroring database.

    Do you have any recommendation?

    Thanks

    My recommendation is this, and no hard feelings but I'm going to be blunt: Managing a 1.4TB table is not easy. Same goes for replication (even more so I think since is is a complex and rather finicky subsystem). Add in mirroring just to make it more hairy. Someone responsible for those things. should know the answers to your questions. But if not certainly be able to find them with a quick web search.

    Replication: https://msdn.microsoft.com/en-us/library/ms151740.aspx

    Mirroring is based off of copying/replaying tlog activity. Rebuilding an index is logged activity --> mirroring will send and replay it. What that means for your mirrored database depends on a number of factors which I don't have.

    I note that a basic question has gone unasked: what is your proof that index fragmentation is causing performance problems on this system?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 22, 2016 at 6:50 am

    #1873885

    how badly are the indexes fragmented? Have you considered reorganising rather than rebuilding, it will be less intrusive, you can then at least control log growth and cancel it if you have to without losing work already done.

    If you reorganise update your stats as well, it might even be stats that are out of date rather than fragmentation is your issue, so check when they were last updated.

    ---------------------------------------------------------------------

  • April 22, 2016 at 8:42 am

    #1873923

    Check to see if the indexes are being used, or if they are duplicated, or if you can create better indexes, i.e. consolidate.

    It would be horrible to rebuild 800GB of unused indexes.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • April 22, 2016 at 8:50 am

    #1873929

    MadAdmin (4/22/2016)

    Check to see if the indexes are being used, or if they are duplicated, or if you can create better indexes, i.e. consolidate.

    It would be horrible to rebuild 800GB of unused indexes.

    +1

  • April 22, 2016 at 9:51 am

    #1873966

    MadAdmin (4/22/2016)

    Check to see if the indexes are being used, or if they are duplicated, or if you can create better indexes, i.e. consolidate.

    It would be horrible to rebuild 800GB of unused indexes.

    Do remember though that index usage stats are reset after SQL Server startup. I have come across quite a few that miss or didn't know that.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 22, 2016 at 6:00 pm

    #1874076

    kiran.patel07 (4/21/2016)

    we did not rebuild index from last 2 Years on those huge tables and it causes performance issue now.

    Probably not. It's likely that the performance is getting slower simply because you have two years of growth.

    So need to rebuild index.

    Again, probably not. The first question you should ask is, have STATISTICS ever been rebuilt?

    Shifting gears a bit, if you're going to insist that you need to rebuild the indexes on the table, then stop and think about possibly partitioning the table especially if it's some form of "WORM" (or nearly so) table where only the current month of data might suffer an update. That would make it so that you could also partition the indexes which would cut way down on index rebuilds. Done correctly, you could set the older partitions to READ_ONLY and never have to reindex them nor build stats on them nor even ever have to back them up ever again. Huge savings in maintenance time all the way around.

    But, first, try rebuilding the stats first.

    --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)

  • April 25, 2016 at 5:34 am

    #1874190

    Thanks all for their suggestion. Help me a lot to understand what are different ways and risks.

    Now,we have decided to reorganize indexes or tell development team to partition table.

    We have to do meeting with client and Application team to make concrete plan.

    I will also post our solution once we come up with all the ideas.

    Thanks again.

  • September 19, 2017 at 8:16 pm

    #1960071

    kiran_Sqldba - Monday, April 25, 2016 5:34 AM

    Thanks all for their suggestion. Help me a lot to understand what are different ways and risks.Now,we have decided to reorganize indexes or tell development team to partition table.We have to do meeting with client and Application team to make concrete plan.I will also post our solution once we come up with all the ideas.Thanks again.

    I know this is over a year old but how'd that work out for you?  Did performance actually improve after doing a reorg on your indexes?

    --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)

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

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