Dbcc indexdefrag

• yentha.valla

  SSC Enthusiast

  Points: 147

  More actions

  April 28, 2011 at 4:25 am

  #389401

  Hi

  Am new to sql serever .. i hv a backp of my prod db ..i need to do defrag the undex fo my prod database.

  so can i directly use dbcc idexdefrag('db','table','inex') ???

  bcz when i run dbcc showconting i found lot of fragmentation is there in the database ..

  please suggest ...

  thanks

  Valla

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 28, 2011 at 4:42 am

  #1317145

  Valla

  If you are using SQL Server 2005, you should use dm_db_index_Physical_stats to determine fragmentation and ALTER INDEX to rebuild or reorganise, since the DBCC commands are deprecated. The most common recommendation I've seen is to reorganise those indexes with fragmentation above 5%, and rebuild above 30%.

  John

• yentha.valla

  SSC Enthusiast

  Points: 147

  More actions

  April 28, 2011 at 5:28 am

  #1317169

  hi,

  Am using 2000 .. so let know what is the commad i hv to use ..

  or do u hv any scripts to rebuild the indesx above 30%??

  Thanks

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 28, 2011 at 5:56 am

  #1317180

  Please make sure you post in the correct forum to avoid misunderstandings like this.

  You need to use DBCC SHOWCONTIG to get fragmentation information, and DBCC DBREINDEX to rebuild your indexes. You should be able to find a script on this site or elesewhere that will reindex according to fragmentation levels.

  Alternatively, you can just create a database maintenance plan. That will rebuild all indexes when it is run.

  John