Post reply

Reindexing taking 20 hours to complete

  • June 17, 2008 at 2:04 pm

    #187293

    I have a very large database and it's taking more than 30 hours to complete the reindexing job thru SQL Server 2005 maintenance plan.

    Is there any script where it can do only to the tables which were updated from the last reindexing.

    Please reply or can post the script if any one knows.

    Thanks,

    KA

  • June 17, 2008 at 2:11 pm

    #830041

    The best idea would be doing a selective reindexing, means rather than doing all tables in the database for reindex select only those tables which are fragmented for re indexing.

    use sys.dm_db_index_physical_stats to find the level of fragmentation in each table and depends on that do the reindexint (all these can be incorporated in the script)

    Also you can explore the ONLINE reindexing option if it is SQL 2005 enterprise edition

    Explore the option of Reorganize rather using rebuild

    Explore the option of SORT IN TEMPDB option to faster the rebuild index process.


    subban

  • June 17, 2008 at 2:16 pm

    #830045

    I would recommend you to use the DMV sys.dm_db_index_physical_stats to check the index fragmentation and then only rebuild or reorganize the indexes that are fragmented. I think that you this way could achieve what you're asking for.

    http://msdn.microsoft.com/en-us/library/ms189858.aspx

    I have a stored procedure that could help you with this.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    You could also use the script in Books Online.

    http://msdn.microsoft.com/en-us/library/ms188917.aspx

    Ola Hallengren

    http://ola.hallengren.com

  • June 17, 2008 at 3:24 pm

    #830084

    I'd go with the above advice. You could even schedule this to run only on selective tables, meaning only do tables that start with a-f on one day, g-m on the other day, etc. And then only that have a certain amount of fragmentation OR when the fillfactor falls to some threshhold.

    You really have to determine what the issues are with your database and which tables need to be changed. You could also track updates/inserts per table based on row counts and determine if you think reindexing is needed.

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

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