Post reply

Index Rebuilding

  • October 29, 2010 at 8:32 am

    #74141

    Dear All,

    Can someone please advise on my question.

    I ran a Index rebuilding query on one table and it's taking more than 9 hrs...please advise what is the issue behind this and if we stop it in middle while it's running, what will happen in next time i.e, when we run a query ,will it starts from scratch or from last ?

    Thanks and Regards,

    Ravichandra.

  • October 29, 2010 at 9:06 am

    #1244263

    If the rebuild process takes 9 hours, this means it needs 9 hours.

    If you stop it, it will start from scratch.

    I suggest that you start using a different optimisation strategy, for instance, rebuilding only the indexes that really need rebuilding.

    Check the IndexOptimisation.sql at http://ola.hallengren.com/[/url]

    -- Gianluca Sartori

  • November 1, 2010 at 2:58 am

    #1244729

    Dear Gianluca Sartori,

    Thanks for the reply.

    The link which you refered to me is very useful and also it's nice script.

    When Iam trying to get the avg_fragmentation_in_percent by using the below query it's taking very huge time to provide result.

    SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

    i.name AS IndexName,

    indexstats.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats

    INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

    AND i.index_id = indexstats.index_id

    WHERE indexstats.avg_fragmentation_in_percent > 30

    Please advise whether there is any query to get quick output.

    Thanks and Regards,

    Ravichandra.

  • November 1, 2010 at 3:14 am

    #1244731

    Change DETAILED to LIMITED

    If both that and the rebuild are taking forever either the table is huge or your IO subsystem is not up to the load, or both.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 1, 2010 at 5:47 am

    #1244771

    Thanks GailShaw.

    Yes, you are right, the table size is 30 GB & indexes size on that table 35 GB.

    I have one more doubt.

    Why data(.mdf) & Log(.ldf) files sizes will be increased after index rebuilt?

    Please clarify me.

    Regards,

    Ravichandra.

  • November 1, 2010 at 6:02 am

    #1244775

    Because SQL needs somewhere to put the new index and because index rebuilds are fully logged in full recovery.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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