Post reply

Re-indexing tables

  • May 25, 2011 at 8:17 am

    #238759

    Hi folks,

    can anyone help me, how to estimate the time to re-index a table which is 160GB(3 indexes).

    I need to re-index all those 3 indexes.

    TIA..

  • May 25, 2011 at 8:21 am

    #1329682

    Does you need to rework the clustered index as well?

    Assuming the server and SAN are not busy doing something else, I'd figure out how much time it takes to write 1 GB to the san then take that figure times 320... so on our SAN it would take 5-6 hours.

    You could also do a test with 1% of the data and see how long that takes (do a select into a new table). Create all 3 index and start all 3 rebuilds and see how long that takes.

    Assume it might take 125 to 150 more time to run with the full data so you have a buffer to work with.

  • May 25, 2011 at 8:26 am

    #1329692

    Thanks for your reply,

    How can i test 1% of data, and moreover those three are non clustured indexes.

    Can u help me with the code...

  • May 25, 2011 at 8:29 am

    #1329694

    srinath.vanama (5/25/2011)

    Thanks for your reply,

    How can i test 1% of data, and moreover those three are non clustured indexes.

    Can u help me with the code...

    SELECT TOP 1 PERCENT * INTO dbo.TestReindex FROM dbo.160GbTable

    CREATE INDEX 1,2,3 ON dbo.TestReindex

    Then rebuild all 3 indexes and see how long it takes (ironically enough it should be close to the time it takes to create them...)

Viewing 4 posts - 1 through 3 (of 3 total)

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