dynamic index rebuild based on fragmentation level

• chewychewy

  Hall of Fame

  Points: 3126

  More actions

  July 16, 2013 at 4:00 am

  #395147

  Hi Guys,

  Anyone have a dynamic script which will list and rebuild indexes > 30% avg fragmentation.

  Seen it somewhere but can't recall....

  thanks

• dbafromthecold@gmail.com

  SSChampion

  Points: 10160

  More actions

  July 16, 2013 at 6:09 am

  #1632829

  Hi, Ola Hallengren has a really good set of scripts for this:-

  http://ola.hallengren.com/

  Andrew

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 16, 2013 at 6:49 am

  #1632844

  Here is one I use .. it does NOT do what you requested as far as rebuilding, but you should be able to modify it to perform that task. Further I am NOT the author of the proc, and I failed to keep the individuals name, for which I apologize.

  CREATE PROCEDURE [dbo].[IndexFragmentation]

  AS

  SELECT OBJECT_NAME(OBJECT_ID) AS Tablename,s.name AS Indexname

  ,index_type_desc

  ,avg_fragmentation_in_percent

  ,page_count

  FROM sys.dm_db_index_physical_stats(5,DEFAULT,DEFAULT,DEFAULT,DEFAULT) d

  join sysindexes s ON d.OBJECT_ID = s.id

  and d.index_id = s.indid

  --and s.name = 'IX_lOGENTRIES' included for a single specific index otherwise reports all indexes

  Hope this helps you.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• hakim.ali

  SSCarpal Tunnel

  Points: 4239

  More actions

  July 17, 2013 at 10:41 am

  #1633382

  If you haven't found a solution yet, give this a shot:

  http://www.sqlservercentral.com/scripts/Fragmentation/90703/

  Hakim Ali
  www.sqlzen.com