Post reply

dynamic index rebuild based on fragmentation level

  • 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

  • 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

  • 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]

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply