Post reply

questions on index rebuilds

  • September 26, 2019 at 7:56 am

    Go to Answer

    #3683524

    Hi Experts,

    Need some clarity on Index rebuilds.

    1. Does Rebuilding a clustered index , will rebuild all non-clustered indexes as well on that table? What is the locking behavior during clustered index REBUILD operation? Will the table is available for user access during rebuild?

    2. if I choose rebuilding a specific non-clustered index then only that particular index will not be available but other structures i mean base table clustered index is available for user access? Is that correct?

    3. What is the best time to perform the index rebuilds. Reason, I am asking is, these indexes are very large indexes. I am seeing some performance issues, if this is run during week-days. The problem , few support engineers are blindly running index rebuild jobs if a user complains about db performance issue.

    SQL Version : SQL 2012,2014,2016 EE

    Thanks,

    Sam

  • September 26, 2019 at 8:31 am

    Answer

    #3683531

    Sam

    1.  Yes, it will.  Locking depends on whether you rebuild with the ONLINE=ON option (only available in Enterprise Edition).  The table will be available during index rebuilds only with ONLINE=ON.
    2. That's right (although the above applies with respect to ONLINE=ON).
    3. First, determine whether you need to rebuild indexes at all.  If there is no fragmentation, you don't need to do anything.  If there is low fragmentation, consider REORGANIZE instead.  And in certain cases, it may be better not to do any index maintenance at all (Jeff Moden has written extensively about this).  Finally, make sure you restrict who has access to do this stuff - you can't just have people rebuilding indexes at busy times just because they think (or, more likely, hope) that it will fix a performance problem.

    John

  • September 26, 2019 at 9:39 am

    #3683542

    Thank you John.

  • September 26, 2019 at 10:29 am

    #3683544

    vsamantha35 wrote:

    1. Does Rebuilding a clustered index, will rebuild all non-clustered indexes as well on that table?

    After rebuilding a clustered index you might also need to rebuild all the non-clustered indexes as the rebuild of the clustered can fragment the non-clustered indexes.

  • September 26, 2019 at 11:42 am

    #3683560

    If I am doing the OFFLINE rebuild ( which default behaviour) will the locks will held for the entire duration of the rebuild operation?

  • September 26, 2019 at 12:28 pm

    #3683587

    Don't forget about statistics. The reason to do an index rebuild is, in theory, to reduce fragmentation. However, as a side benefit, you get rebuilt statistics. It's entirely possible that, instead of needing to rebuild indexes over and over all day long, you just need a statistics update. While not free, they are radically lower cost than an index rebuild.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 6 (of 6 total)

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