Database re-indexing - best practices advice sought

Posted on 2004-11-16
Last Modified: 2008-02-01
I have a 15GB database which has a daily maintenance plan to optimise the tables by setting their fill factors back to 90%.

The problem is that it creates a huge amount of database transactions. The next transaction log backup after the reindex comes to 11GB!

What is the best way to run reindexing? Should I write a script to put the database into simple recovery model and then run the reindex? But if I did that, how would that affect the continuity of any potential restore operation I may need to run? Because I would be switching the database back and forth between simple and bulk-logged recovery model.

First question: What is the best way to make reindexing and transaction log backups co-exist without the reindexing operation blowing out the transaction log size?

Second question: Should I even be running a reindex every day or would once a week suffice? I'm thinking daily is best because the database is a major production system which is hit by many users every day.
Question by:meumax

    Expert Comment

    Rebuilding Index online is not a issue from Oracle9i use ONLINE option with ALTER INDEX REBUILD.


    This will allow you rebuild indexes without distrubing the usage of IDX_DEPT_NO and adding NOLOGGING option will avoide from generating redo logs. PARALLEL option will enable parallel executution.

    Rebuilding index daily is a painful job. better you can use


    Now you can verify the index LMODE from index_stats dictionary view and decide for rebuilding indexes.


    Velu N
    LVL 3

    Author Comment

    Oops. Should have mentioned which rdbms I'm using. It's actually SQL Server 2000.
    LVL 7

    Accepted Solution

    I think you may be running your maintenance operation a little too frequently.

    The need to do this operation depends on:
    - frequency of index updates
    - observered application performance

    If your application users aren't reporting performance issues then you could consider dropping the frequency down to weekly (or even monthly), then setup some monitoring to see if performance is significantly impacted.

    Consider dropping the fill factor down (say to 75% or 80%). That will increase the space used for indexes and increase the number of pages that may need to be traversed fro a query but will reduce the level of fragmentation that occurs - so can have less frequent index rebuilds.

    Consider the use of DBCC INDEXDEFRAG as an alternative to ReIndex. It's an online operation, and is more efficient on lightly fragmented indexes. It will  take longer for a heavily fragmented index but
    - no locks are held so user transactions are not blocked.
    - the use of short transactions allows log file size to be minimised through use of frequent log backups during the operation or use of SIMPLE recovery model.

    As both INDEXDEFRAG and REINDEX allow you to specify individual tables to target, you could mix and match use of these utilities based on your knowledge of application usage.

    Scott Pettman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now