rebuild inexes

• srimkumarp

  Old Hand

  Points: 359

  More actions

  December 13, 2007 at 10:25 pm

  #185003

  hi ,

  i am having some doubts regarding rebuild and reorgainze indexes.

  after run the rebuild and reorganize,

  i run this smt . for avg_page_space_used_in_percent value is null for all indexes and for some tables when i run alter rebuild statements the output is not varying same as previous ones.

  SELECT distinct a.index_id, name, avg_fragmentation_in_percent,avg_fragment_size_in_pages,avg_page_space_used_in_percent

  FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'etrans'),

  NULL, NULL, NULL) AS a

  JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

  is it compulsory to run update statitics after running rebuild and reorganize?

  pls help someone .

  thanks ,

  kumar p

• Big Data Man

  SSCrazy Eights

  Points: 8536

  More actions

  December 14, 2007 at 6:00 am

  #760185

  Hey,

  For index rebuilds, the stats are updated as part of index rebuild process, but not for indexdefrag (e.g reorg.). If you opt for a reorg vs a rebuild, you will need to update the stats as the next step following reorg only.

  You should run the following on a regular basis to understand amount of fragmentation:

  sys.dm_db_index_physical_stats

  Thanks,

  Phillip Cox

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  December 14, 2007 at 6:01 am

  #760186

  if you examine your table/indexes you'll be able to see if the stats are up to date.

  I'll always run sp_updatestats on a database every day if I have the window ( this only updates stats which need updating )

  other options I use is to step through my indexes rebuilding or updating stats. Depending on you index rebuild command stats are usually updated.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/