Index Rebuild - Job running over 16 hours.....

• jackimo

  SSCommitted

  Points: 1885

  More actions

  October 30, 2011 at 4:59 pm

  #1400907

  Thanks Jeff - It's Standard Edition. I'll add the clustered index tomorrow on EventTime and look into indexed views.

  Once I have something to report I'll update the post!

  Thanks everyone for all their help!

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  October 30, 2011 at 5:07 pm

  #1400908

  jackimo (10/30/2011)

  ---

  Thanks Jeff - It's Standard Edition. I'll add the clustered index tomorrow on EventTime and look into indexed views.

  Once I have something to report I'll update the post!

  Thanks everyone for all their help!

  Don't waste your time on adding the clustered index until you read about indexed views! 😉

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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 30, 2011 at 5:44 pm

  #1400913

  David Moutray (10/28/2011)

  ---

  Dropping an index and then recreating it will also bypass the transaction log, however.

  Nope and creating a clustered index is fully logged in full recovery. Everything in SQL is logged. Dropping then recreating the clustered index will incur far more logging than just rebuilding it, as the drop requires that all nonclustered indexes be rebuild, the create also requires that all the nonclustered indexes be rebuild.

  So, log impact for rebuild of a clustered index = size of the clustered index + overhead

  Log impact for drop and create of clustered index = 2*total size of all nonclustered indexes + size of clustered index + overhead

  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