Post reply

rebuild index for 1 table at 1 time?

  • April 8, 2014 at 4:22 am

    #292068

    Hi,

    I have a huge DB with 30 tables out of which 10 are extremely huge tables. From the index physical statistics report i can see some tables indexes are highly fragmented (above 70%). because these tables are huge i would like to rebuild index for one table at a time, could you let me know if this is possible? this is for sql server 2008 standard

    Thanks

  • April 8, 2014 at 4:38 am

    #1704081

    ALTER INDEX ALL ON <Table Name> REBUILD

    or

    ALTER INDEX <index name> ON <Table Name> REBUILD

    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

  • April 8, 2014 at 4:46 am

    #1704083

    many thanks

  • April 8, 2014 at 4:54 am

    #1704087

    Yes, it will be good idea to perform reindexing on a larger table with respect to specific index or table as mention by Gail. Thus it will be easy to monitor if any blocking or load on the server.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • April 9, 2014 at 4:45 am

    #1704436

    Does the DB have to be offline when in rebuild indexes on this table? This a 300GB fact table , what will happen if any application issues a select statement on the table when index is being rebuilt? Do i have to ensure no once is accessing the tables when rebuild happens?

    Also any idea how much time it takes to rebuild index for such a huge tables? we have a beefy server 8CPU and 24GB RAM but its sql server standard edition.

  • April 9, 2014 at 5:06 am

    #1704442

    The database can't be offline as you can not run any commands against an offline database.

    Rebuilds take locks (unless you run them with the ONLINE option) and hence will block any queries using the tables. No way to estimate time, as it's dependent on too many factors.

    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