none
SQL script to rebuild all indexes in DB

    Question

  • Hi,

    On my sql server so number of database are created.

    on my one database i have check avg_fragmentation_in_percent which is greater than 30% now i want to Rebuild Index on all tables within a specific database as given in following link

    http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx

    please tell me script to rebuild index of all table within given database.
    iffi
    Tuesday, January 03, 2012 7:09 AM

Answers

  • Hi,

     

    Before implementing an index fragmentation maintenance plan, you need to understand which tables and indexes are most fragmented and then create a maintenance plan to rebuild or reorganize those indexes.

     

    In SharePoint 2010, an example of a table that often becomes fragmented is AllDocs, which contains document libraries, their associated documents and lists and list items, and their respective metadata.

     

    I prefer rebuild index on database level instead of table level except you know each table very well.

     

    Addition information:

     

    Database Maintenance for Microsoft SharePoint 2010 Products

    http://www.microsoft.com/download/en/details.aspx?id=24282

     

    Thanks,

    Rock Wang


    Rock Wang TechNet Community Support
    • Marked as answer by Wayne Fan Friday, January 13, 2012 3:36 AM
    Wednesday, January 04, 2012 9:49 AM

All replies

  • There are lots of such script samples on the internet, for example, this one:

     

    DECLARE @TableName VARCHAR(255)
    DECLARE @sql NVARCHAR(500)
    DECLARE @fillfactor INT
    SET @fillfactor = 80
    DECLARE TableCursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
    FROM sys.tables
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)
    FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
    GO
    

    taken from here: http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

     

    Remember to run it on your DB (or add USE <Database Name> at the beginning).

    • Proposed as answer by Amol Meshe Tuesday, January 03, 2012 10:35 AM
    Tuesday, January 03, 2012 10:24 AM
  • Hi,

    thanks.

    what are the recommendation for index rebuild for sharepoint content database. should i rebuild index of complete database or just apply at table level.

    pls suggest.

    what would be benefits in terms of performance gain.

     


    iffi
    Tuesday, January 03, 2012 10:52 AM
  • Hi,

     

    Before implementing an index fragmentation maintenance plan, you need to understand which tables and indexes are most fragmented and then create a maintenance plan to rebuild or reorganize those indexes.

     

    In SharePoint 2010, an example of a table that often becomes fragmented is AllDocs, which contains document libraries, their associated documents and lists and list items, and their respective metadata.

     

    I prefer rebuild index on database level instead of table level except you know each table very well.

     

    Addition information:

     

    Database Maintenance for Microsoft SharePoint 2010 Products

    http://www.microsoft.com/download/en/details.aspx?id=24282

     

    Thanks,

    Rock Wang


    Rock Wang TechNet Community Support
    • Marked as answer by Wayne Fan Friday, January 13, 2012 3:36 AM
    Wednesday, January 04, 2012 9:49 AM