Rebuild index Necessary?

• jchandramouli

  SSCommitted

  Points: 1772

  More actions

  March 26, 2009 at 4:08 am

  #103713

  Hi,

  A function that fetches some records from table containing 3555000 records took nearly a minute. I gave an index and rebuild it. The time reduced drastically.

  My question is whether the rebuilding of index is mandatory after creating index or does it serves some other purpose.

  Thanks

  "I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 26, 2009 at 4:46 am

  #966518

  Hi

  Directly after the creation of the index you don't have to rebuild it. This re-creates the index 😛

  But you have to monitor the index fragmentation and its scan density and rebuild it when it becomes slow.

  Greets

  Flo

• Grant Fritchey

  SSC Guru

  Points: 398711

  More actions

  March 26, 2009 at 5:25 am

  #966530

  As Flo says, you shouldn't have to rebuild the index right after creating it.

  Are you sure the index is working well with the query? Did the execution plan change before and after the creation of the index?

  "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

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 26, 2009 at 5:47 am

  #966552

  jchandramouli (3/26/2009)

  ---

  Hi,

  A function that fetches some records from table containing 3555000 records took nearly a minute. I gave an index and rebuild it. The time reduced drastically.

  My question is whether the rebuilding of index is mandatory after creating index or does it serves some other purpose.

  Thanks

  By the way... what do you mean with "fetch". I hope you mean a SELECT. Do you use a CURSOR?

  Greets

  Flo